<?php

namespace App\V3\V3_2_6\Controllers;

use App\Helper\DBRSAUtil;
use App\Http\Controllers\Controller;
use App\V3\V3_2_6\Models\MaterielModel;
use App\V3\V3_2_6\Models\ProductOrderModel;
use App\V3\V3_2_6\Models\SalesReturnModel;
use App\V3\V3_2_6\Models\SellModel;
use App\V3\V3_2_6\Models\SystemConfigModel;
use App\V3\V3_2_6\Models\AdminModel;
use App\V3\V3_2_6\Services\CommonStorageOperateService;
use App\V3\V3_2_6\Services\LogService;
use App\V3\V3_2_6\Services\MaterielsService;
use App\V3\V3_2_6\Services\StorageManageService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use App\V3\V3_2_6\Services\SellsService;
use Illuminate\Validation\Rule;
use OSS\OssClient;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\V3\V3_2_6\Models\SerialNumberModel;
use App\V3\V3_2_6\Models\SellGoodsDetailBatchModel;
use App\V3\V3_2_6\Models\SellPackageBatchModel;
use App\V3\V3_2_6\Services\SalesReturnsService;
use App\V3\V3_2_6\Services\StatisticsService;
use App\V3\V3_2_6\Models\TallyModel;
use App\V3\V3_2_6\Services\OverflowBreakageService;
use App\V3\V3_2_6\Models\AdminAddendumModel;
use App\V3\V3_2_6\Models\MaterielTypeModel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use App\V3\V3_2_6\Models\MaterielClassModel;
use App\V3\V3_2_6\Models\TallyClassModel;
class CommonsController extends Controller
{
    protected $ossClient;

    public function __construct(Request $request)
    {

        $this->ossClient = new OssClient(
            config('app.aliyun_oss_key'),
            config('app.aliyun_oss_secret'),
            config('app.aliyun_oss_endpoint')
        );
    }

    public function readAddress(Request $request)
    {
        if(!empty($content = $request->input('content'))) {
            $newContent = preg_replace('/[^\x{4e00}-\x{9fa5}a-zA-Z0-9]/u'," ",$content);

            $contentArr = array_filter(explode(' ', $newContent), function($v) { return $v !== ''; });

            if(empty($contentArr[0])) {
                return response()->json(
                    ['code' => 1, 'msg' => '姓名不能为空', 'data' => '']
                );
            }

            $data['name'] = $contentArr[0];
            $data['mobile'] = empty($contentArr[1])?'':$contentArr[1];
            $data['address'] = empty($contentArr[2])?'':$contentArr[2];

            if(!empty($contentArr[1])) {
                $checkMobileInfo = self::checkMobile($data);

                if(!empty($checkMobileInfo)) {
                    return response()->json([
                        'code' => 1,
                        'msg' => $checkMobileInfo,
                        'data' => ''
                    ]);
                }
            }

            return response()->json(
                ['code' => 0, 'msg' => 'success', 'data' => $data]
            );
        }
        else {
            return response()->json(
                ['code' => 0, 'msg' => 'success', 'data' => '']
            );
        }
    }

    /**
     * 查询物料商品信息（zlf）
     * @param goods_id 物料商品
     */
    public function getGoodsInfo(Request $request){
        $messages = [
            'goods_id.required' => '商品id,不能为空',
            'goods_id.integer' => '商品id,为整数',
        ];

        $validator = Validator::make($data = $request->all(), [
            'goods_id' => 'required|integer',
        ], $messages);

        $msg = $validator->errors()->first();

        if(!empty($msg)) return $msg;
        $info = MaterielTypeModel::where($data)->where('delete_flag',0)->frist();
        return response()->json(['code'=>0,'success','data'=>$info]);
    }

    private function checkMobile($data) {
        $messages = [
            'mobile.required' => '手机号不能为空，请输入手机号',
            'mobile.regex' => '手机号格式错误，请重试',
        ];

        $validator = Validator::make($data, [
            'mobile' => 'required|regex:/^1[3456789][0-9]{9}$/',
        ], $messages);

        return $validator->errors()->first();
    }

    /**
     * 导出excel-销售记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/10 10:48
     * @return array|void
     */
    public function sellExport(Request $request)
    {
        $data = $request->all();
        if(empty($data['token'])) {
            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        //获取导出的数据
        $exportData = SellsService::exportData($array[0], $data);
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('销售记录表');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:AL1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        /* $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); */

        $spreadsheet->getActiveSheet()->getStyle('A:AL')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:AL')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(15);



            $sheet->setCellValue('A1', '日期')
            ->setCellValue('B1', '序列')
            ->setCellValue('C1', '操作人昵称')
            ->setCellValue('D1', '操作人账号')
            ->setCellValue('E1', '收货人')
            ->setCellValue('F1', '订单编号')
            ->setCellValue('G1', '出库状态')
            ->setCellValue('H1', '付款状态')
            ->setCellValue('I1', '备注')
            ->setCellValue('J1', '销售时间')
            ->setCellValue('K1', '单位')
            ->setCellValue('L1', '渠道')
            ->setCellValue('M1', '商品类型')
            ->setCellValue('N1', '类别')
            ->setCellValue('O1', '规格')
            ->setCellValue('P1', '数量')
            ->setCellValue('Q1', '销售价（元）')
            ->setCellValue('R1', '成本价（元）')
            ->setCellValue('S1', '商品单位')
            ->setCellValue('T1', '仓库名称')
            ->setCellValue('U1', '序列号')
            ->setCellValue('V1', '折扣 ：百分比')
            ->setCellValue('W1', '税率 ：百分比')
            ->setCellValue('X1', '货款金额（元）')
            ->setCellValue('Y1', '其他费用（元）')
            ->setCellValue('Z1', '实付金额（元）')
            ->setCellValue('AA1', '总计金额（元）')
            ->setCellValue('AB1', '付款公司名称')
            ->setCellValue('AC1', '发货方式')
            ->setCellValue('AD1', '运输方式')
            ->setCellValue('AE1', '物流公司')
            ->setCellValue('AF1', '物流单号')
            ->setCellValue('AG1', '结算重量')
            ->setCellValue('AH1', '计价重量')
            ->setCellValue('AI1', '运费（元）')
            ->setCellValue('AJ1', '件数');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('U')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('V')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('W')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('X')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Y')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Z')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AA')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AB')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AC')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AD')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AE')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AF')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AG')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AH')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AI')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AJ')->getAlignment()->setWrapText(true);


        $goodsNum = [];

        foreach($exportData as $key => $value) {

            if(!empty($value['materiel_info'])){//商品信息

                $goodsNum[] = count($value['materiel_info']);

                if(empty($value['out_status'])){
                    $out_status_msg='待出库';
                }elseif ($value['out_status'] == 1){
                    $out_status_msg='部分出库';
                }elseif ($value['out_status'] == 2){
                    $out_status_msg='全部出库';
                }

                if(empty($value['pay_status'])){
                    $pay_status_msg='待付款';
                }elseif ($value['pay_status'] == 1){
                    $pay_status_msg='部分付款';
                }elseif ($value['pay_status'] == 2){
                    $pay_status_msg='全部付款';
                }

                if($value['deliver_goods_type'] == 1){
                    $deliver_goods_type_msg='自提';
                }elseif ($value['deliver_goods_type'] == 2){
                    $deliver_goods_type_msg='代发';
                }elseif ($value['deliver_goods_type'] == 3){
                    $deliver_goods_type_msg='送货';
                }else{
                    $deliver_goods_type_msg='';
                }

                if($value['transport_type'] == 1){
                    $transport_type_msg='空运';
                }elseif ($value['transport_type'] == 2){
                    $transport_type_msg='海运';
                }elseif ($value['transport_type'] == 3){
                    $transport_type_msg='铁路';
                }elseif ($value['transport_type'] == 4){
                    $transport_type_msg='公路';
                }else{
                    $transport_type_msg='';
                }

                foreach ($value['materiel_info'] as $k => $item) {
                    //销售商品

                    $condition = [];
                    $condition['type_id']   = $item['materiel_type_id'];
                    $condition['b.sell_id']   = $item['sell_id'];
                    $condition['b.main_id'] = $value['main_id'];

                    $goodsSerialList = SerialNumberModel::select('serial_no')
                    ->from('serial_number as a')
                    ->leftJoin('sell_out_serial_numbers as b','b.serial_number','=','a.serial_no')
                    ->where($condition)
                    ->where('a.status',0)
                    ->get()->toArray();

                    if(!empty($goodsSerialList)) $goodsSerialList = implode(',', array_map('array_shift',$goodsSerialList));

                  /*   if($item['is_product_materiel'] == 1){
                        $type = '套餐商品';
                    }else{
                        $type = '普通商品';
                    } */

                    $mInfo = MaterielClassModel::where(['id'=>$item['materiel_class_id']])->first();

                    $list[] = [
                        'o_date'             => date('m',time()).'月'.date('d',time()).'号',//日期
                        'nickname'           => $value['nickname'],//操作人昵称
                        'username'           => $value['username'],//操作人账号
                        'consignee'          => $value['consignee'].','.$value['phone'].','.$value['address'],//收货人
                        'order_no'           => $value['order_no'], //订单编号
                        'out_status'         => $out_status_msg,//出库状态
                        'pay_status'         => $pay_status_msg,//付款状态
                        'remark'             => $value['remark'],//备注
                        'sell_date'          => $value['sell_date'],//销售时间
                        'unit'               => '睡猫云仓',//单位
                        'channel'            => '睡猫云仓', //渠道
                        'goods_type'         => "普通商品",//商品类型
                        'goods_class'        => $mInfo['class_name'],//类别
                        'goods_info'         => $item['type_name'].'，'.$item['name'],//规格
                        'num'                => $item['num'],//数量
                        'price'              => $item['price'],//销售价
                        'cost_price'         => $item['cost_price'],//成本价
                        'piece'              => $item['piece'],//单位
                        'warehouse'          => $item['warehouse'].','.$item['shelf_name'].','.$item['location_name'],//仓库
                        'serial_list'        => !empty($goodsSerialList)?$goodsSerialList:"",//序列号
                        'discount'           => $value['discount'],//折扣 ：百分比
                        'tax_rate'           => $value['tax_rate'],//税率 ：百分比
                        'money'              => $value['money'],//货款金额（元）
                        'other_charges'      => $value['other_charges'],//其他费用（元）
                        'real_money'         => $value['real_money'],   //实付金额
                        'final_money'        => $value['final_money'],//总计金额
                        'paying_company'     => "",//付款公司名称
                        'deliver_goods_type' => $deliver_goods_type_msg,//发货方式
                        'transport_type'     => $transport_type_msg,//运输方式
                        'logistics_company'  => $value['logistics_company'],//物流公司
                        'logistics_order_no' => $value['logistics_order_no'], //物流单号
                        'settlement_weight'  => "",//结算重量
                        'valuation_weight'   => "",//计价重量
                        'freight'            => "",//运费
                        'number_cases'       => "",//件数
                    ];

                }
            }
        }

        $cell = ['A', 'B', 'C', 'D','E','F','G','H','I','J','K','L','M','N', 'Y','Z','AA','AB', 'AC', 'AD','AE','AF','AG','AH','AI','AJ'];//需要合并的单元格

        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value)-1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
        }

        if(!empty($list)){
           $i=1;
            foreach($list as $key => $value) {

                if($key>0){
                    if($list[$key-1]['order_no'] != $value['order_no']){
                        $i++;
                    }
                }

                $key += 2;
                $sheet->setCellValue('A'.$key, $value['o_date'])
                    ->setCellValue('B'.$key, $i)
                    ->setCellValue('C'.$key, $value['nickname'])
                    ->setCellValue('D'.$key, $value['username'])
                    ->setCellValue('E'.$key, $value['consignee'])
                    ->setCellValue('F'.$key, $value['order_no'])
                    ->setCellValue('G'.$key, $value['out_status'])
                    ->setCellValue('H'.$key, $value['pay_status'])
                    ->setCellValue('I'.$key, $value['remark'])
                    ->setCellValue('J'.$key, $value['sell_date'])
                    ->setCellValue('K'.$key, $value['unit'])
                    ->setCellValue('L'.$key, $value['channel'])
                    ->setCellValue('M'.$key, $value['goods_type'])
                    ->setCellValue('N'.$key, $value['goods_class'])
                    ->setCellValue('O'.$key, $value['goods_info'])
                    ->setCellValue('P'.$key, $value['num'])
                    ->setCellValue('Q'.$key, $value['price'])
                    ->setCellValue('R'.$key, $value['cost_price'])
                    ->setCellValue('S'.$key, $value['piece'])
                    ->setCellValue('T'.$key, $value['warehouse'])
                    ->setCellValue('U'.$key, $value['serial_list'])
                    ->setCellValue('V'.$key, $value['discount'])
                    ->setCellValue('W'.$key, $value['tax_rate'])
                    ->setCellValue('X'.$key, $value['money'])
                    ->setCellValue('Y'.$key, $value['other_charges'])
                    ->setCellValue('Z'.$key, $value['real_money'])
                    ->setCellValue('AA'.$key, $value['final_money'])
                    ->setCellValue('AB'.$key, $value['paying_company'])
                    ->setCellValue('AC'.$key, $value['deliver_goods_type'])
                    ->setCellValue('AD'.$key, $value['transport_type'])
                    ->setCellValue('AE'.$key, $value['logistics_company'])
                    ->setCellValue('AF'.$key, $value['logistics_order_no'])
                    ->setCellValue('AG'.$key, $value['settlement_weight'])
                    ->setCellValue('AH'.$key, $value['valuation_weight'])
                    ->setCellValue('AI'.$key, $value['freight'])
                    ->setCellValue('AJ'.$key, $value['number_cases']);

            }

        }
        return self::downloadExcel($spreadsheet, '销售记录表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-退货记录
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/04 14:04
     * @return array|void
     */
    public function salesReturnExport(Request $request){
        $data = $request->all();
        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'search_type'=>[
                'required',
                Rule::In([1,2,3,4,5,6,7,8])
            ],
            'status'=>[
                'required',
                Rule::In([0,1,2,3,4,5,6])
            ],
            'repeal_status'=>[
                'required',
                Rule::In([0,1])
            ]
        ],[
            'search_type.required' => '参数`搜索类型`缺失',
            'search_type.in' => '参数`搜索类型`不合法',
            'status.required' => '参数`状态`缺失',
            'status.in' => '参数`状态`不合法',
            'repeal_status.required' => '参数`撤销状态`缺失',
            'repeal_status.in' => '参数`撤销状态`不合法',
        ]);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => '',
            ]);
        }

        if(isset($request->start_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'start_time' => 'date_format:Y-m-d H:i:s',//页面
            ],[
                'start_time.date_format' => '开始时间格式错误，例：'.date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json([
                    'code' => 1,
                    'msg' => $infos,
                    'data' => '',
                ]);
            }
        }

        if(isset($request->end_time)) {
            $timeValidator = Validator::make($data = $request->all(), [
                'end_time' => 'date_format:Y-m-d H:i:s',//页面
            ],[
                'end_time.date_format' => '结束时间格式错误，例：'.date('Y-m-d H:i:s')
            ]);

            //参数错误响应
            if ($infos = $timeValidator->errors()->first()) {
                return response()->json([
                    'code' => 1,
                    'msg' => $infos,
                    'data' => '',
                ]);
            }
        }

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);

        if($adminInfo['parent_id'] != 0) {

            $data['user_id'] = $array[0];
            $data['main_id'] = $adminInfo['parent_id'];
        }else {
            $data['user_id'] = $array[0];
            $data['main_id'] = $array[0];
        }

        if($array[0] == $adminInfo['parent_id']){
            $userId['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }else {
            $userId['a.user_id'] = ['EQ' => $array[0]];
        }
        //获取导出的数据


        $exportData = SalesReturnsService::exportData($userId, $data);
       /*  echo "<pre>";
        var_dump($exportData);die; */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('退货记录');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:AS1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:AS')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:AS')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AD')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AH')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AI')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('AK')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AL')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AM')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AN')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AO')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AP')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AQ')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AR')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('AS')->setWidth(30);

        $sheet->setCellValue('A1', '订单号')
        ->setCellValue('B1', '操作人昵称')
        ->setCellValue('C1', '操作人账号')
        ->setCellValue('D1', '客户姓名')
        ->setCellValue('E1', '收货人')
        ->setCellValue('F1', '联系电话')
        ->setCellValue('G1', '收货地址')
        ->setCellValue('H1', '折扣 ：百分比')
        ->setCellValue('I1', '税率 ：百分比')
        ->setCellValue('J1', '实际应收金额（元）')
        ->setCellValue('K1', '实收金额（元）')
        ->setCellValue('L1', '货款金额（元）')
        ->setCellValue('M1', '备注')
        ->setCellValue('N1', '入库状态')
        ->setCellValue('O1', '付款状态')
        ->setCellValue('P1', '退货时间')
        ->setCellValue('Q1', '商品信息')
        ->setCellValue('R1', '商品名称')
        ->setCellValue('S1', '商品型号')
        ->setCellValue('T1', '销售价（元）')
        ->setCellValue('U1', '商品成本（元）')
        ->setCellValue('V1', '商品数量')
        ->setCellValue('W1', '入库数量')
        ->setCellValue('X1', '在库数量')

        ->setCellValue('Y1', '仓库名称')
        ->setCellValue('Z1', '货架名称')
        ->setCellValue('AA1', '库位名称')
        ->setCellValue('AB1', '序列号')
        ->setCellValue('AC1', '批次号')
        ->setCellValue('AD1', '套餐信息')
        ->setCellValue('AE1', '套餐名称')
        ->setCellValue('AF1', '售价（元）')
        ->setCellValue('AG1', '套餐总成本（元）')
        ->setCellValue('AH1', '套餐数量')
        ->setCellValue('AI1', '已入库套餐数量')
        ->setCellValue('AJ1', '商品名称')
        ->setCellValue('AK1', '规格名称')
        ->setCellValue('AL1', '数量')
        ->setCellValue('AM1', '在库数量')
        ->setCellValue('AN1', '仓库名称')
        ->setCellValue('AO1', '仓库地址')
        ->setCellValue('AP1', '货架名称')
        ->setCellValue('AQ1', '库位名称')
        ->setCellValue('AR1', '序列号')
        ->setCellValue('AS1', '批次号');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('U')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('V')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('W')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('X')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Y')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Z')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AA')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AB')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AC')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AD')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AE')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AF')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AG')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AH')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AI')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AJ')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AK')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AL')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AM')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AN')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AO')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AP')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AQ')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AR')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('AS')->getAlignment()->setWrapText(true);
        $goodsNum = [];
        foreach ($exportData as $key=>$value){
            if(empty($value['out_status'])){
                $out_status_msg='待入库';
            }elseif ($value['out_status'] == 1){
                $out_status_msg='部分入库';
            }elseif ($value['out_status'] == 2){
                $out_status_msg='全部入库';
            }

            if(empty($value['pay_status'])){
                $pay_status_msg='待付款';
            }elseif ($value['pay_status'] == 1){
                $pay_status_msg='部分付款';
            }elseif ($value['pay_status'] == 2){
                $pay_status_msg='全部付款';
            }

            if(!empty($value['return_materiel']) && empty($value['return_package'])){//只有退货商品

                $goodsSerialList = "";
                $goodsBatchList  = "";
                $goodsNum[] = count($value['return_materiel']);
                foreach ($value['return_materiel'] as $item){

                    if(!empty($item['goods_serial_info'])){//序列号

                        foreach ($item['goods_serial_info'] as $gk=>$gv){

                        $goodsSerialList .=$gv['serial_number'].',';
                        }

                        $goodsSerialList = substr($goodsSerialList,0,strrpos($goodsSerialList,","));
                    }

                    if(!empty($item['goods_batch_info'])){//批次号

                        foreach ($item['goods_batch_info'] as $bk=>$bv){

                            $goodsBatchList .=$bv['batch_no'].',';
                        }

                        $goodsBatchList = substr($goodsBatchList,0,strrpos($goodsBatchList,","));
                    }
                    $list[] = [
                            'order_no'          => $value['order_no'],
                            'nickname'           => $value['nickname'],
                            'username'           => $value['username'],
                            'customer_name'      => $value['customer_name'],
                            'consignee'          => $value['consignee'],
                            'phone'              => $value['phone'],
                            'address'            => $value['address'],
                            'discount'           => $value['discount'],
                            'tax_rate'           => $value['tax_rate'],
                            'final_money'        => $value['final_money'],
                            'real_money'         => $value['real_money'],
                            'money'              => $value['money'],
                            'remark'             => $value['remark'],
                            'out_status'         => $out_status_msg,
                            'pay_status'         => $pay_status_msg,
                            'refund_date'        => $value['refund_date'],
                            'goods_info'         => '商品信息>>>',
                            'name'               => $item['name'],
                            'type_name'          => $item['type_name'],
                            'price'              => $item['price'],
                            'cost_price'         => $item['cost_price'],
                            'num'                => $item['num'],
     	                    'in_num'             => $item['in_num'],
    	                    'in_storage_num'     => $item['in_storage_num'],
                            'warehouse'          => $item['warehouse'],
                            'shelf_name'         => $item['shelf_name'],
                            'location_name'      => $item['location_name'],
                            'serial_list'        => $goodsSerialList,
                             'batch_list'        => $goodsBatchList,
                            'tc_info'            => '套餐信息>>>',
                            'package_name'       => "",
                            'tc_price'           => "",
                            'tc_cost_price'      => "",
                            'tc_num'             => "",
                            'tc_in_num'          => "",
                            'tc_materiel_name'   => "",
                            'tc_type_name'       => "",
                            'tc_goods_num'       => "",
                            'tc_in_storage_num'  => "",
                            'storage_name'       => "",
                	        'storage_address'    => "",
                            'tc_shelf_name'      => "",
                            'tc_location_name'   => "",
                            'tc_serial_list'     => "",
                            'tc_batch_list'      => ""
                        ];
                }
            }elseif(!empty($value['return_materiel']) && !empty($value['return_package'])){//退货商品和退货套餐都有

                foreach ($value['return_package'] as $pk => $pv){

                if(!empty($pv['package_content']) && count($value['return_materiel']) >= count($pv['package_content'])){//退货商品数量大于等于套餐商品数量

                    $goodsNum[] = count($value['return_materiel']);

                foreach ($value['return_materiel'] as $k=>$item){
                    $goodsSerialList = "";
                    $goodsBatchList  = "";
                    $packageSerialList = "";
                    $packageBatchList  = "";
                    //退货商品序列号和批次号
                    if(!empty($item['goods_serial_info'])){//序列号

                        foreach ($item['goods_serial_info'] as $gk=>$gv){

                            $goodsSerialList .= $gv['serial_number'].',';
                        }

                        $goodsSerialList = substr($goodsSerialList,0,strrpos($goodsSerialList,","));
                    }
                    if(!empty($item['goods_batch_info'])){//批次号

                        foreach ($item['goods_batch_info'] as $bk=>$bv){

                            $goodsBatchList .= $bv['batch_no'].',';
                        }

                        $goodsBatchList = substr($goodsBatchList,0,strrpos($goodsBatchList,","));
                    }

                    //退货套餐的序列号和批次号
                    if(!empty($pv['package_content'][$k]['package_serial_info'])){//序列号

                        foreach ($pv['package_content'][$k]['package_serial_info'] as $sk=>$sv){

                            $packageSerialList .= $sv['serial_number'].',';
                        }

                        $packageSerialList = substr($packageSerialList,0,strrpos($packageSerialList,","));

                    }
                    if(!empty($pv['package_content'][$k]['package_batch_info'])){//批次号

                        foreach ($pv['package_content'][$k]['package_batch_info'] as $bk=>$bv){

                            $packageBatchList .= $bv['batch_no'].',';
                        }

                        $packageBatchList = substr($packageBatchList,0,strrpos($packageBatchList,","));
                    }

                    $list[] = [
                        'order_no'          => $value['order_no'],
                        'nickname'           => $value['nickname'],
                        'username'           => $value['username'],
                        'customer_name'      => $value['customer_name'],
                        'consignee'          => $value['consignee'],
                        'phone'              => $value['phone'],
                        'address'            => $value['address'],
                        'discount'           => $value['discount'],
                        'tax_rate'           => $value['tax_rate'],
                        'final_money'        => $value['final_money'],
                        'real_money'         => $value['real_money'],
                        'money'              => $value['money'],
                        'remark'             => $value['remark'],
                        'out_status'         => $out_status_msg,
                        'pay_status'         => $pay_status_msg,
                        'refund_date'        => $value['refund_date'],
                        'goods_info'         => '商品信息>>>',
                        'name'               => $item['name'],
                        'type_name'          => $item['type_name'],
                        'price'              => $item['price'],
                        'cost_price'         => $item['cost_price'],
                        'num'                => $item['num'],
                        'in_num'             => $item['in_num'],
                        'in_storage_num'     => $item['in_storage_num'],
                        'warehouse'          => $item['warehouse'],
                        'shelf_name'         => $item['shelf_name'],
                        'location_name'      => $item['location_name'],
                        'serial_list'        => $goodsSerialList,
                        'batch_list'         => $goodsBatchList,
                        'tc_info'            => '套餐信息>>>',
                        'package_name'       => !empty($pv['package_name'])?$pv['package_name']:"",
                        'tc_price'           => !empty($pv['price'])?$pv['price']:"",
                        'tc_cost_price'      => !empty($pv['cost_price'])?$pv['cost_price']:"",
                        'tc_num'             => !empty($pv['num'])?$pv['num']:"",
                        'tc_in_num'          => !empty($pv['in_num'])?$pv['in_num']:"",
                        'tc_materiel_name'   => !empty($pv['package_content'][$k]['materiel_name'])?$pv['package_content'][$k]['materiel_name']:"",
                        'tc_type_name'       => !empty($pv['package_content'][$k]['type_name'])?$pv['package_content'][$k]['type_name']:"",
                        'tc_goods_num'       => !empty($pv['package_content'][$k]['num'])?$pv['package_content'][$k]['num']:"",
                        'tc_in_storage_num'  => !empty($pv['package_content'][$k]['in_storage_num'])?$pv['package_content'][$k]['in_storage_num']:"",
                        'storage_name'       => !empty($pv['package_content'][$k]['storage_name'])?$pv['package_content'][$k]['storage_name']:"",
                        'storage_address'    => !empty($pv['package_content'][$k]['storage_address'])?$pv['package_content'][$k]['storage_address']:"",
                        'tc_shelf_name'      => !empty($pv['package_content'][$k]['shelf_name'])?$pv['package_content'][$k]['shelf_name']:"",
                        'tc_location_name'   => !empty($pv['package_content'][$k]['location_name'])?$pv['package_content'][$k]['location_name']:"",
                        'tc_serial_list'     => !empty($packageSerialList)?$packageSerialList:"",
                        'tc_batch_list'      => !empty($packageBatchList)?$packageBatchList:""
                    ];
                }
                }else{
                    //退货商品数量小于套餐商品数量
                    $goodsNum[] = count($pv['package_content']);

                    foreach ($pv['package_content'] as $pck=>$pcv){

                        $goodsSerialList = "";
                        $goodsBatchList  = "";
                        $packageSerialList = "";
                        $packageBatchList  = "";

                            //退货商品序列号和批次号
                            if(!empty($value['return_materiel'][$pck]['goods_serial_info'])){//序列号

                                foreach ($value['return_materiel'][$pck]['goods_serial_info'] as $gk=>$gv){

                                    $goodsSerialList .= $gv['serial_number'].',';
                                }

                                $goodsSerialList = substr($goodsSerialList,0,strrpos($goodsSerialList,","));
                            }
                            if(!empty($value['return_materiel'][$pck]['goods_batch_info'])){//批次号

                                foreach ($value['return_materiel'][$pck]['goods_batch_info'] as $bk=>$bv){

                                    $goodsBatchList .= $bv['batch_no'].',';
                                }

                                $goodsBatchList = substr($goodsBatchList,0,strrpos($goodsBatchList,","));
                            }

                        //退货套餐的序列号和批次号
                        if(!empty($pcv['package_serial_info'])){//序列号

                            foreach ($pcv['package_serial_info'] as $sk=>$sv){

                                $packageSerialList .= $sv['serial_number'].',';
                            }

                            $packageSerialList = substr($packageSerialList,0,strrpos($packageSerialList,","));
                        }
                        if(!empty($pcv['package_batch_info'])){//批次号

                            foreach ($pcv['goods_batch_info'] as $bk=>$bv){

                                $packageBatchList .= $bv['batch_no'].',';
                            }

                            $packageBatchList = substr($packageBatchList,0,strrpos($packageBatchList,","));
                        }

                        $list[] = [
                            'order_no'          => $value['order_no'],
                            'nickname'           => $value['nickname'],
                            'username'           => $value['username'],
                            'customer_name'      => $value['customer_name'],
                            'consignee'          => $value['consignee'],
                            'phone'              => $value['phone'],
                            'address'            => $value['address'],
                            'discount'           => $value['discount'],
                            'tax_rate'           => $value['tax_rate'],
                            'final_money'        => $value['final_money'],
                            'real_money'         => $value['real_money'],
                            'money'              => $value['money'],
                            'remark'             => $value['remark'],
                            'out_status'         => $out_status_msg,
                            'pay_status'         => $pay_status_msg,
                            'refund_date'        => $value['refund_date'],
                            'goods_info'         => '商品信息>>>',
                            'name'               => !empty($exportData[$key]['return_materiel'][$pck]['name'])?$exportData[$key]['return_materiel'][$pck]['name']:"",
                            'type_name'          => !empty($exportData[$key]['return_materiel'][$pck]['type_name'])?$exportData[$key]['return_materiel'][$pck]['type_name']:"",
                            'price'              => !empty($exportData[$key]['return_materiel'][$pck]['price'])?$exportData[$key]['return_materiel'][$pck]['price']:"",
                            'cost_price'         => !empty($exportData[$key]['return_materiel'][$pck]['cost_price'])?$exportData[$key]['return_materiel'][$pck]['cost_price']:"",
                            'num'                => !empty($exportData[$key]['return_materiel'][$pck]['num'])?$exportData[$key]['return_materiel'][$pck]['num']:"",
                            'in_num'             => !empty($exportData[$key]['return_materiel'][$pck]['in_num'])?$exportData[$key]['return_materiel'][$pck]['in_num']:"",
                            'in_storage_num'     => !empty($exportData[$key]['return_materiel'][$pck]['in_storage_num'])?$exportData[$key]['return_materiel'][$pck]['in_storage_num']:"",
                            'warehouse'          => !empty($exportData[$key]['return_materiel'][$pck]['warehouse'])?$exportData[$key]['return_materiel'][$pck]['warehouse']:"",
                            'shelf_name'         => !empty($exportData[$key]['return_materiel'][$pck]['shelf_name'])?$exportData[$key]['return_materiel'][$pck]['shelf_name']:"",
                            'location_name'      => !empty($exportData[$key]['return_materiel'][$pck]['location_name'])?$exportData[$key]['return_materiel'][$pck]['location_name']:"",
                            'serial_list'        => $goodsSerialList,
                            'batch_list'         => $goodsBatchList,
                            'tc_info'            => '套餐信息>>>',
                            'package_name'       => !empty($pv['package_name'])?$pv['package_name']:"",
                            'tc_price'           => !empty($pv['price'])?$pv['price']:"",
                            'tc_cost_price'      => !empty($pv['cost_price'])?$pv['cost_price']:"",
                            'tc_num'             => !empty($pv['num'])?$pv['num']:"",
                            'tc_in_num'          => !empty($pv['in_num'])?$pv['in_num']:"",
                            'tc_materiel_name'   => !empty($pcv['materiel_name'])?$pcv['materiel_name']:"",
                            'tc_type_name'       => !empty($pcv['type_name'])?$pcv['type_name']:"",
                            'tc_goods_num'       => !empty($pcv['num'])?$pcv['num']:"",
                            'tc_in_storage_num'  => !empty($pcv['in_storage_num'])?$pcv['in_storage_num']:"",
                            'storage_name'       => !empty($pcv['storage_name'])?$pcv['storage_name']:"",
                            'storage_address'    => !empty($pcv['storage_address'])?$pcv['storage_address']:"",
                            'tc_shelf_name'      => !empty($pcv['shelf_name'])?$pcv['shelf_name']:"",
                            'tc_location_name'   => !empty($pcv['location_name'])?$pcv['location_name']:"",
                            'tc_serial_list'     => !empty($packageSerialList)?$packageSerialList:"",
                            'tc_batch_list'      => !empty($packageBatchList)?$packageBatchList:""
                        ];
                    }
                }
            }
            }elseif(empty($value['return_materiel']) && !empty($value['return_package'])){//退货套餐都

                foreach ($value['return_package'] as $pk=>$pv){

                        $goodsNum[] = count($pv['package_content']);

                        foreach ($pv['package_content'] as $cv){
                            $packageSerialList = "";
                            $packageBatchList  = "";

                            //退货套餐的序列号和批次号
                            if(!empty($cv['package_serial_info'])){//序列号

                                foreach ($cv['package_serial_info'] as $sk=>$sv){

                                    $packageSerialList .= $sv['serial_number'].',';
                                }

                                $packageSerialList = substr($packageSerialList,0,strrpos($packageSerialList,","));
                            }
                            if(!empty($cv['package_batch_info'])){//批次号

                                foreach ($cv['package_batch_info'] as $bk=>$bv){

                                    $packageBatchList .= $bv['batch_no'].',';
                                }

                                $packageBatchList = substr($packageBatchList,0,strrpos($packageBatchList,","));
                            }

                            $list[] = [
                                'order_no'          =>  $value['order_no'],
                                'nickname'           => $value['nickname'],
                                'username'           => $value['username'],
                                'customer_name'      => $value['customer_name'],
                                'consignee'          => $value['consignee'],
                                'phone'              => $value['phone'],
                                'address'            => $value['address'],
                                'discount'           => $value['discount'],
                                'tax_rate'           => $value['tax_rate'],
                                'final_money'        => $value['final_money'],
                                'real_money'         => $value['real_money'],
                                'money'              => $value['money'],
                                'remark'             => $value['remark'],
                                'out_status'         => $out_status_msg,
                                'pay_status'         => $pay_status_msg,
                                'refund_date'        => $value['refund_date'],
                                'goods_info'         => '商品信息>>>',
                                'name'               => "",
                                'type_name'          => "",
                                'price'              => "",
                                'cost_price'         => "",
                                'num'                => "",
                                'in_num'             => "",
                                'in_storage_num'     => "",
                                'warehouse'          => "",
                                'shelf_name'         => "",
                                'location_name'      => "",
                                'serial_list'        => "",
                                'batch_list'         => "",
                                'tc_info'            => '套餐信息>>>',
                                'package_name'       => !empty($pv['package_name'])?$pv['package_name']:"",
                                'tc_price'           => !empty($pv['price'])?$pv['price']:"",
                                'tc_cost_price'      => !empty($pv['cost_price'])?$pv['cost_price']:"",
                                'tc_num'             => !empty($pv['num'])?$pv['num']:"",
                                'tc_in_num'          => !empty($pv['in_num'])?$pv['in_num']:"",
                                'tc_materiel_name'   => !empty($cv['materiel_name'])?$cv['materiel_name']:"",
                                'tc_type_name'       => !empty($cv['type_name'])?$cv['type_name']:"",
                                'tc_goods_num'       => !empty($cv['num'])?$cv['num']:"",
                                'tc_in_storage_num'  => !empty($cv['in_storage_num'])?$cv['in_storage_num']:"",
                                'storage_name'       => !empty($cv['storage_name'])?$cv['storage_name']:"",
                                'storage_address'    => !empty($cv['storage_address'])?$cv['storage_address']:"",
                                'tc_shelf_name'      => !empty($cv['shelf_name'])?$cv['shelf_name']:"",
                                'tc_location_name'   => !empty($cv['location_name'])?$cv['location_name']:"",
                                'tc_serial_list'     => !empty($packageSerialList)?$packageSerialList:"",
                                'tc_batch_list'      => !empty($packageBatchList)?$packageBatchList:""
                            ];
                        }
                }
            }
        }

        $cell = ['A', 'B', 'C', 'D','E','F','G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q','AD','AE','AF','AG','AH','AI'];//需要合并的单元格

        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value)-1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }
        }

        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['order_no'])
                ->setCellValue('B'.$key, $value['nickname'])
                ->setCellValue('C'.$key, $value['username'])
                ->setCellValue('D'.$key, $value['customer_name'])
                ->setCellValue('E'.$key, $value['consignee'])
                ->setCellValue('F'.$key, $value['phone'])
                ->setCellValue('G'.$key, $value['address'])
                ->setCellValue('H'.$key, $value['discount'])
                ->setCellValue('I'.$key, $value['tax_rate'])
                ->setCellValue('J'.$key, $value['final_money'])
                ->setCellValue('K'.$key, $value['real_money'])
                ->setCellValue('L'.$key, $value['money'])

                ->setCellValue('M'.$key, $value['remark'])
                ->setCellValue('N'.$key, $value['out_status'])
                ->setCellValue('O'.$key, $value['pay_status'])
                ->setCellValue('P'.$key, $value['refund_date'])
                //商品信息
                ->setCellValue('Q'.$key, $value['goods_info'])
                ->setCellValue('R'.$key, $value['name'])
                ->setCellValue('S'.$key, $value['type_name'])
                ->setCellValue('T'.$key, $value['price'])
                ->setCellValue('U'.$key, $value['cost_price'])
                ->setCellValue('V'.$key, $value['num'])
                ->setCellValue('W'.$key, $value['in_num'])
                ->setCellValue('X'.$key, $value['in_storage_num'])
                ->setCellValue('Y'.$key, $value['warehouse'])
                ->setCellValue('Z'.$key, $value['shelf_name'])
                ->setCellValue('AA'.$key, $value['location_name'])
                ->setCellValue('AB'.$key, $value['serial_list'])
                ->setCellValue('AC'.$key, $value['batch_list'])
                //套餐信息
                ->setCellValue('AD'.$key, $value['tc_info'])
                ->setCellValue('AE'.$key, $value['package_name'])
                ->setCellValue('AF'.$key, $value['tc_price'])
                ->setCellValue('AG'.$key, $value['tc_cost_price'])
                ->setCellValue('AH'.$key, $value['tc_num'])
                ->setCellValue('AI'.$key, $value['tc_in_num'])
                ->setCellValue('AJ'.$key, $value['tc_materiel_name'])
                ->setCellValue('AK'.$key, $value['tc_type_name'])
                ->setCellValue('AL'.$key, $value['tc_goods_num'])
                ->setCellValue('AM'.$key, $value['tc_in_storage_num'])
                ->setCellValue('AN'.$key, $value['storage_name'])
                ->setCellValue('AO'.$key, $value['storage_address'])
                ->setCellValue('AP'.$key, $value['tc_shelf_name'])
                ->setCellValue('AQ'.$key, $value['tc_location_name'])
                ->setCellValue('AR'.$key, $value['tc_serial_list'])
                ->setCellValue('AS'.$key, $value['tc_batch_list']);
            }
        }
        return SELF::downloadExcel($spreadsheet, '退货记录_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-商品采购表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/28 14:04
     * @return array|void
     */
    public function goodsPurchaseExport(Request $request){
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
        ],[
            'start_time.date_format' => '开始日期格式有误，例：'.date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：'.date('Y-m-d'),
        ]);
        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);
        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code'=>1,'msg'=>$info,'data'=>'']);
        }

        if(!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0].' 00:00:00');
        }

        if(!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0].' 23:59:59');
        }

        $condition = [];

        if(!empty($startTime) && !empty($endTime)) {
            $condition[] = ['b.purchase_time', '>=', $startTime];
            $condition[] = ['b.purchase_time', '<=', $endTime];
        }
        else if(!empty($startTime) && empty($endTime)) {
            $condition[] = ['b.purchase_time', '>=', $startTime];
        }
        else if(empty($startTime) && !empty($endTime)) {
            $condition[] = ['b.purchase_time', '<=', $endTime];
        }

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);

        $userInfo = [];
        if($adminInfo['id'] == $adminInfo['parent_id']){
            $userInfo['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }else {
            $userInfo['a.user_id'] = ['EQ' => $adminInfo['id']];
        }

        $exportData = StatisticsService::statementMaterielExport($condition, $data, $userInfo);
        /* echo "<pre>";
        var_dump($exportData);die; */

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('商品采购统计报表');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:T1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);

        $sheet->setCellValue('A1', '商品名称（规格）')
        ->setCellValue('B1', '采购价格（元）')
        ->setCellValue('C1', '采购数量')
        ->setCellValue('D1', '总金额（元）')
        ->setCellValue('E1', '总数量');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];

            $materielLine[] = count($exportData['materiel_content_info']);

            foreach($exportData['materiel_content_info'] as $val) {
                $list[] = [
                    'materiel_name' => $val['materiel_name'].'/'.$val['type_name'].'（'.$val['materiel_no'].'）',
                    'total_unit_price' => $val['total_unit_price'],
                    'num' => $val['num'],
                    'total_amount' => $exportData['total_amount'],
                    'total_price' => $exportData['total_price']
                ];
        }

        $cell = ['D','E'];//需要合并的单元格
        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value)-1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['materiel_name'])
                ->setCellValue('B'.$key, $value['total_unit_price'])
                ->setCellValue('C'.$key, $value['num'])
                ->setCellValue('D'.$key, $value['total_price'])
                ->setCellValue('E'.$key, $value['total_amount']);
            }
        }
        return SELF::downloadExcel($spreadsheet, '商品采购统计表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-已销售的商品统计
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/29 14:04
     * @return array|void
     */
    public function statisticsGoodsSoldExport(Request $request){
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' =>[
                'required',
                Rule::In([0,1]),//成本报表类型:0:商品;1:套餐;
            ],
        ],[

            'start_time.date_format' => '开始日期格式有误，例：'.date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：'.date('Y-m-d'),
            'type.required' => '请选择报表类型',
            'type.in' => '报表类型错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code'=>1,'msg'=>$info,'data'=>'']);
        }
        if($data['type'] ==1){

            $title='已销售的商品统计表（套餐）';
        }else{
            $title='已销售的商品统计表（商品）';
        }
        $res = SELF::costStatementHandle($title, $data);
        return response()->json($res);
    }

    /**
     * 导出excel-销售报表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/10/29 14:04
     * @return array|void
     */
    public function statementSellExport(Request $request){
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
        ],[
            'start_time.date_format' => '开始日期格式有误，例：'.date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：'.date('Y-m-d'),
        ]);

        if(empty($data['token'])) {
            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return ['code' => 1, 'msg' => $info, 'data' => ''];
        }

        if(!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0].' 00:00:00');
        }

        if(!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0].' 23:59:59');
        }

        $condition = [];

        if(!empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
            $condition[] = ['sell_date', '<=', $endTime];
        }
        else if(!empty($startTime) && empty($endTime)) {
            $condition[] = ['sell_date', '>=', $startTime];
        }
        else if(empty($startTime) && !empty($endTime)) {
            $condition[] = ['sell_date', '<=', $endTime];
        }

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);

        if($adminInfo['id'] == $adminInfo['parent_id']){
            $condition['main_id'] = ['EQ' => $adminInfo['parent_id']];
        }else {
            $condition['user_id'] = ['EQ' => $adminInfo['id']];
        }
        /* $condition['main_id'] = 9604;
        $condition['user_id'] = 9604; */
       @ini_set('memory_limit', '1024M');
       @ini_set('max_execution_time', 30);
       ob_end_clean();
       ob_start();
       $spreadsheet = new Spreadsheet();
       $sheet = $spreadsheet->getActiveSheet();
       $spreadsheet->getDefaultStyle();
       $sheet->setTitle("销售报表");

       //A1-L1字体加粗
       $spreadsheet->getActiveSheet()->getStyle('A1:K1')->getFont()->setBold(true);
       //设置字体
       $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
       //设置字体大小
       $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
       //设置默认行高
       $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

       $spreadsheet->getActiveSheet()->getStyle('A:K')->getAlignment()->setHorizontal('center');
       $spreadsheet->getActiveSheet()->getStyle('A:K')->getAlignment()->setVertical('center');

       //设置列宽
       $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(50);
       $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
       $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
       $sheet->setCellValue('A1', '客户姓名')
       ->setCellValue('B1', '订单号')
       ->setCellValue('C1', '销售金额（元）')
       ->setCellValue('D1', '占总额百分比（%）')
       ->setCellValue('E1', '销售时间')
       ->setCellValue('F1', '总金额（元）')
       ->setCellValue('G1', '数量')
       ->setCellValue('H1', '成本')
       ->setCellValue('I1', '利润')
       ->setCellValue('J1', '总成本')
       ->setCellValue('K1', '总利润');

       //设置自动换行
       $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
       $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);

       $exportData = StatisticsService::statementSellExport($condition, $data);
       
       $materielLine = [];
       $list = [];

       $materielLine[] = count($exportData['sell_info']);
       IF(!empty($exportData['sell_info'])){
            foreach($exportData['sell_info'] as $val) {
               $list[] = [
                   'customer_name' => $val['customer_name'],
                   'order_no' => $val['order_no'],
                   'final_money' => $val['final_money'],
                   'percentage_total' => (number_format($val['final_money']/$exportData['total_money']*100,2)).'%',
                   'sell_date' => $val['sell_date'],
                   'total_money' => $exportData['total_money'],
                   'goods_num' => $val['goods_num'],
                   'cost_price' => $val['cost_price'],
                   'profit' => $val['profit'],
                   'total_cost_price' => $exportData['total_cost_price'],
                   'total_profit' => $exportData['total_profit']
               ];
            }
       }
       $cell = ['F','J','K'];//需要合并的单元格
       if(!empty($list)){
           foreach ($cell as $item) {
               $start = 2;
               foreach ($materielLine as $k => $value) {
                   $end = ($start + $value)-1;
                   $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                   $start = $end + 1;
               }
           }
       }
       if(!empty($list)){
           foreach($list as $key => $value) {
               $key += 2;
               $sheet->setCellValue('A'.$key, $value['customer_name'])
               ->setCellValue('B'.$key, $value['order_no'])
               ->setCellValue('C'.$key, $value['final_money'])
               ->setCellValue('D'.$key, $value['percentage_total'])
               ->setCellValue('E'.$key, $value['sell_date'])
               ->setCellValue('F'.$key, $value['total_money'])
               ->setCellValue('G'.$key, $value['goods_num'])
               ->setCellValue('H'.$key, $value['cost_price'])
               ->setCellValue('I'.$key, $value['profit'])
               ->setCellValue('J'.$key, $value['total_cost_price'])
               ->setCellValue('K'.$key, $value['total_profit']);
           }
       }
       return SELF::downloadExcel($spreadsheet, '销售报表_'.date('Y-m-d'), 'Xlsx');
    }
    /**
     * 导出excel-成本报表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 9:30
     * @return array|void
     */
    public function costStatementExport(Request $request){
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' =>[
                'required',
                Rule::In([0,1]),//成本报表类型:0:商品;1:套餐;
            ],
        ],[

            'start_time.date_format' => '开始日期格式有误，例：'.date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：'.date('Y-m-d'),
            'type.required' => '请选择报表类型',
            'type.in' => '报表类型错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code'=>1,'msg'=>$info,'data'=>'']);
        }
        if($data['type'] ==1){

            $title='成本报表（套餐）';
        }else{
            $title='成本报表（商品）';
        }
        $res = SELF::costStatementHandle($title, $data);
        return response()->json($res);
    }

    /**
     * 导出excel-支出(收入)明细表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 13:48
     * @return array|void
     */
    public function incomeExpenditureExport(Request $request){

        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'start_time' => 'nullable|date_format:Y-m-d',//开始时间
            'end_time' => 'nullable|date_format:Y-m-d',//开始时间
            'type' =>[
                'required',
                Rule::In([0,1]),
            ],//收支类型：0-收入 1-支出
        ],[
            'start_time.date_format' => '开始日期格式有误，例：'.date('Y-m-d'),
            'end_time.date_format' => '结束日期格式有误，例：'.date('Y-m-d'),
            'type.required' => '`报表类型`缺失',
            'type.in' => '`报表类型`错误',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return $this->fail('', $info);
        }

        if(!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0].' 00:00:00');
        }

        if(!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0].' 23:59:59');
        }

        $condition = [];

        if(!empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
            $condition[] = ['add_time', '<=', $endTime];
        }
        else if(!empty($startTime) && empty($endTime)) {
            $condition[] = ['add_time', '>=', $startTime];
        }
        else if(empty($startTime) && !empty($endTime)) {
            $condition[] = ['add_time', '<=', $endTime];
        }

        $condition['type'] = ['EQ' => $data['type']];

        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);;

        if($adminInfo['parent_id'] == 0) {

            $condition['main_id'] = ['EQ' => $adminInfo['id']];
        }else{
            $condition['main_id'] = ['EQ' => $adminInfo['parent_id']];
        }

        $totalInfo = TallyModel::selectRaw(
            'SUM(money) as money'
        )->where($condition);

        $incomeList = TallyModel::selectRaw(
            'add_time,money,tally_class_name'
        )->where($condition);

        $tallyClassId = [];
        if(isset($data['tally_class_id'])) {
            $tallyClassInfo = TallyClassModel::where('id', $data['tally_class_id'])->first();

            if($tallyClassInfo['parent_id'] == 0) {
                //获取二级几张类型信息
                $getTallyClass = TallyClassModel::where('parent_id', $data['tally_class_id'])->get()->toArray();

                if(!empty($getTallyClass)) {
                    foreach($getTallyClass as $value) {
                        $tallyClassId[] = $value['id'];
                    }
                }
            }
            else {
                $tallyClassId[] = $data['tally_class_id'];
            }

            $totalInfo->whereIn('tally_class_id', $tallyClassId);

            $incomeList->whereIn('tally_class_id', $tallyClassId);
        }

        $totalInfo = $totalInfo->groupBy('main_id')->first();

        $exportData['total_money'] = empty($totalInfo)?0:$totalInfo->money;

        $exportData['income_list'] = $incomeList->orderBy('money', 'desc')
        ->get()->toArray();

       /*  $totalInfo = TallyModel::selectRaw(
            'SUM(money) as money'
            )->where($condition)->groupBy('main_id')->first();

            $exportData['total_money'] = empty($totalInfo)?0:$totalInfo->money;
            $exportData['income_list'] = TallyModel::selectRaw(
                'add_time,money,tally_class_name'
                )->where($condition)
                ->get()->toArray(); */

              /*   echo "<pre>";
                var_dump($exportData);die;  */

                if($data['type'] == 1){
                    $title="支出";
                }else{
                    $title="收入";
                }
                @ini_set('memory_limit', '1024M');
                @ini_set('max_execution_time', 30);
                ob_end_clean();
                ob_start();
                $spreadsheet = new Spreadsheet();
                $sheet = $spreadsheet->getActiveSheet();
                $spreadsheet->getDefaultStyle();
                $sheet->setTitle($title.'明细表');

                //A1-L1字体加粗
                $spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);
                //设置字体
                $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
                //设置字体大小
                $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
                //设置默认行高
                $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
                $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setHorizontal('center');
                $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setVertical('center');
                //设置列宽
                $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(50);
                $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
                $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
                $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
                $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);

                $sheet->setCellValue('A1', $title.'类型')
                ->setCellValue('B1', '金额（元）')
                ->setCellValue('C1', '占总额百分比')
                ->setCellValue('D1', '日期')
                ->setCellValue('E1', '总'.$title.'（元）');

                //设置自动换行
                $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
                $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
                $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
                $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
                $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);

                $materielLine = [];
                $list = [];

                $materielLine[] = count($exportData['income_list']);

                foreach($exportData['income_list'] as $val) {

                    $list[] = [
                        'tally_class_name' => $val['tally_class_name'],
                        'money' => $val['money'],
                        'percentage_total' => (number_format($val['money']/$exportData['total_money']*100,2)).'%',
                        'add_time' => $val['add_time'],
                        'total_money' => $exportData['total_money']

                    ];
                }

                $cell = ['E'];//需要合并的单元格
                if(!empty($list)){
                    foreach ($cell as $item) {
                        $start = 2;
                        foreach ($materielLine as $k => $value) {
                            $end = ($start + $value)-1;
                            $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                            $start = $end + 1;
                        }
                    }
                }
                if(!empty($list)){
                    foreach($list as $key => $value) {
                        $key += 2;
                        $sheet->setCellValue('A'.$key, $value['tally_class_name'])
                        ->setCellValue('B'.$key, $value['money'])
                        ->setCellValue('C'.$key, $value['percentage_total'])
                        ->setCellValue('D'.$key, $value['add_time'])
                        ->setCellValue('E'.$key, $value['total_money']);
                    }
                }
                return SELF::downloadExcel($spreadsheet, $title.'明细表_'.date('Y-m-d'), 'Xlsx');

    }

    /**
     * 导出excel-库存统计表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/1 15:05
     * @return array|void
     */
    public function inventoryStatisticsExport(Request $request){
        $data = $request->all();
        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);;

        if($adminInfo['parent_id'] == 0) {

            $data['a.main_id'] = ['EQ' => $adminInfo['id']];
        }else{
            $data['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }

        $exportData = StatisticsService::inventoryStatisticsExport($request->storage_id, $data);
       /*  echo "<pre>";
        var_dump($exportData);die;    */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("库存统计表");
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:C1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
        $spreadsheet->getActiveSheet()->getStyle('A:C')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:C')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);

        $sheet->setCellValue('A1', '商品名称（规格）')
        ->setCellValue('B1', '总件数')
        ->setCellValue('C1', '总价值');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $materielLine = [];
        $list = [];

        $materielLine[] = count($exportData['detail_info']);
        foreach($exportData['detail_info'] as $val) {

            $list[] = [
                'materiel_name' => $val['materiel_name'].'/'.$val['type_name'].'（'.$val['materiel_no'].'）',
                'total_amount' => $val['total_amount'],
                'total_cost' => $val['total_cost']
            ];
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['materiel_name'])
                ->setCellValue('B'.$key, $value['total_amount'])
                ->setCellValue('C'.$key, $value['total_cost']);
            }
        }
        return SELF::downloadExcel($spreadsheet,'库存统计表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 导出excel-仓库统计表
     * @param Request $request
     * AUTHOR  : zlf
     * DateTime: 2022/11/2 10:05
     * @return array|void
     */
    public function statementStorageExport(Request $request){
        $data = $request->all();
        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);;

        if($adminInfo['parent_id'] == 0) {

            $condition['a.main_id'] = ['EQ' => $adminInfo['id']];
        }else{
            $condition['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }

        $exportData = StatisticsService::statementStorageExport($condition);
         /* echo "<pre>";
         var_dump($exportData);die;    */
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("仓库统计表");
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
        $spreadsheet->getActiveSheet()->getStyle('A:D')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:D')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(50);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);

        $sheet->setCellValue('A1', '仓库名称')
        ->setCellValue('B1', '仓库地址')
        ->setCellValue('C1', '总数量')
        ->setCellValue('D1', '总价值');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData['storage_info']);

        foreach($exportData['storage_info'] as $val) {

            $list[] = [
                'storage_name'    => $val['storage_name'],
                'storage_address' => $val['storage_address'],
                'total_amount'    => !empty($val['storage_detail_info']['total_amount'])?$val['storage_detail_info']['total_amount']:"",
                'total_cost'      => !empty($val['storage_detail_info']['total_cost'])?$val['storage_detail_info']['total_cost']:""
            ];
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['storage_name'])
                ->setCellValue('B'.$key, $value['storage_address'])
                ->setCellValue('C'.$key, $value['total_amount'])
                ->setCellValue('D'.$key, $value['total_cost']);
            }
        }
        return SELF::downloadExcel($spreadsheet,'仓库统计表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 客户报表
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/3 15:28
     * @return \Illuminate\Http\JsonResponse
     *
     */
    public function statementCustomerExport(Request $request)
    {
       $data=$request->all();

        if(empty($data['token'])) {

            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);;

        if($adminInfo['parent_id'] == 0 || $adminInfo['parent_id'] == $adminInfo['id']) {

            $condition['main_id'] = ['EQ' => $adminInfo['id']];
        }else{
            $condition['user_id'] = ['EQ' => $adminInfo['id']];
        }

        $exportData = StatisticsService::statementCustomerExport($data, $condition);
        /* echo "<pre>";
        var_dump($exportData);die; */

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("客户统计表");
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:E1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
        $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:E')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $sheet->setCellValue('A1', '客户')
        ->setCellValue('B1', '利润（元）')
        ->setCellValue('C1', '欠款（元）')
        ->setCellValue('D1', '采购（元）')
        ->setCellValue('E1', '销售（元）');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData);

        foreach($exportData as $val) {

            $list[] = [
                'customer_name'    => $val['customer_name'],
                'income_money'      => $val['income_money'],
                'customer_debt'      => $val['customer_debt'],
                'materiel_money' => $val['materiel_money'],
                'sell_money'    => $val['sell_money']
            ];
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['customer_name'])
                ->setCellValue('B'.$key, $value['income_money'])
                ->setCellValue('C'.$key, $value['customer_debt'])
                ->setCellValue('D'.$key, $value['materiel_money'])
                ->setCellValue('E'.$key, $value['sell_money']);
            }
        }
        return SELF::downloadExcel($spreadsheet,'客户统计表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 记账单列表导出
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/4 10:46
     * @return \Illuminate\Http\JsonResponse
     *
     */
    public function tallyListExport(Request $request){
        $data=$request->all();
        if(empty($data['token'])) {
            return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
        }
        $array = explode(' ', $data['token']);
        $adminInfo = AdminModel::where('id', $array[0])->first();
        if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);

        //参数验证
        $validator = Validator::make($data = $request->except('role_id'), [
            'type' => [
                'required',
                'integer',
                Rule::In([0, 1, 2]),
            ],//收支类型：0-收入 1-支出 2-全部
        ],[
            'type.required' => '参数`type`缺失',
            'type.integer' => '参数`type`仅能为数字',
            'type.in' => '参数`type`不合法',
        ]);

        //参数错误响应
        if ($info = $validator->errors()->first()) {
            return response()->json(['code'=>1,'msg'=>$info,'data'=>'']);
        }

        if($adminInfo['parent_id'] == 0 || $adminInfo['parent_id'] == $adminInfo['id']) {
            $condition[] = ['main_id','=',$adminInfo['id']];
        }else{
            $condition[] = ['main_id','=',$adminInfo['parent_id']];
        }
        if($data['type'] != 2){
            $condition[] = ['type','=',$data['type']];
        }
        //查询
        $exportData = TallyModel::where($condition)->orderBy('id','desc')->get()->toArray();

        switch($data['type']){
            case 2:$title="综合";break;
            case 1:$title="支出";break;
            case 0:$title="收入";break;
        }
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle("记账单列表（".$title."）");
        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:D1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
        $spreadsheet->getActiveSheet()->getStyle('A:D')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:D')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);

        $sheet->setCellValue('A1', '记账类型')
        ->setCellValue('B1', '记账金额')
        ->setCellValue('C1', '编号')
        ->setCellValue('D1', '记账日期');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];
        $materielLine[] = count($exportData);

        foreach($exportData as $val) {

            $list[] = [
                'tally_class_name' => $val['tally_class_name'],
                'money'            => $val['money'],
                'order_no'         => $val['order_no'],
                'add_time' => $val['add_time']
            ];
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['tally_class_name'])
                ->setCellValue('B'.$key, $value['money'])
                ->setCellValue('C'.$key, $value['order_no'])
                ->setCellValue('D'.$key, $value['add_time']);
            }
        }
        return SELF::downloadExcel($spreadsheet,'记账单列表（'.$title.'）_'.date('Y-m-d'), 'Xlsx');

    }

    /**
     * 报溢报损记录导出
     * @param Request $request
     * AUTHOR  : ZLF
     * DateTime: 2022/11/5 10:46
     * @return \Illuminate\Http\JsonResponse
     *
     */
   public function overflowBreakageExport(Request $request){

       //参数验证
       $validator = Validator::make($data = $request->all(), [
           'type'=>[//类型：0-报溢 1-报损
               'required',
               Rule::In([0,1])
           ],
           'repeal_status'=>[//撤销状态：0-未撤销 1-已撤销 2-全部
               'required',
               Rule::In([0,1,2])
           ],
           'status'=>[//	审批状态(即入库状态):0-待审批 1-通过 2-拒绝 3-全部
               'required',
               Rule::In([0,1,2,3])
           ],
       ], [
           'type.required' => '参数`类型`缺失',
           'type.In' => '参数`类型`不合法',
           'repeal_status.required' => '参数`撤销状态`缺失',
           'repeal_status.In' => '参数`撤销状态`不合法',
           'status.required' => '参数`审批状态`缺失',
           'status.In' => '参数`审批状态`不合法',
       ]);
       //参数错误响应
       if ($info = $validator->errors()->first()) {
           return response()->json(['code' => 1,'msg' => $info,'data' => '']);
       }

       if(isset($request->start_time)) {
           $timeValidator = Validator::make($data = $request->all(), [
               'start_time' => 'date_format:Y-m-d H:i:s',//页面
           ],[
               'start_time.date_format' => '开始时间格式错误，例：'.date('Y-m-d H:i:s')
           ]);

           //参数错误响应
           if ($infos = $timeValidator->errors()->first()) {
               return response()->json(['code' => 1,'msg' => $infos,'data' => '']);
           }
       }

       if(isset($request->end_time)) {
           $timeValidator = Validator::make($data = $request->all(), [
               'end_time' => 'date_format:Y-m-d H:i:s',//页面
           ],[
               'end_time.date_format' => '结束时间格式错误，例：'.date('Y-m-d H:i:s')
           ]);

           //参数错误响应
           if ($infos = $timeValidator->errors()->first()) {
               return response()->json(['code' => 1,'msg' => $infos,'data' => '']);
           }
       }

       $data = $request->all();
       if(empty($data['token'])) {
           return response()->json(['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>'']);
       }
       $array = explode(' ', $data['token']);
       $adminInfo = AdminModel::where('id', $array[0])->where('delete_flag',0)->first();
       if(empty($adminInfo)) return response()->json(['code'=>1,'msg'=>'导出异常','data'=>'']);

       if($adminInfo['id'] == $adminInfo['parent_id'] || $adminInfo['parent_id'] == 0){

           $data['user_id'] = $adminInfo['id'];
           $data['main_id'] = $adminInfo['id'];
       }else{

           $data['user_id'] = $adminInfo['id'];
           $data['main_id'] = $adminInfo['parent_id'];
       }

      $aaInfo = AdminAddendumModel::select('is_share_breakage','is_share_overflow')
                ->where(['main_id'=>$data['main_id']])->first();

       if($data['user_id'] == $data['main_id']){
           $userId['a.main_id'] = ['EQ' => $data['main_id']];
       }
       else {
           //type类型：0-报溢 1-报损
           if($data['type']) {
               if($aaInfo['is_share_breakage']) {
                   $userId['a.main_id'] = ['EQ' => $data['main_id']];
               }else {
                   $userId['a.user_id'] = ['EQ' => $data['user_id']];
               }
           }
           else {
               if($aaInfo['is_share_overflow']) {
                   $userId['a.main_id'] = ['EQ' => $data['main_id']];
               }else {
                   $userId['a.user_id'] = ['EQ' => $data['user_id']];
               }
           }
       }

       $exportData = OverflowBreakageService::list($data, $userId);
      /*  $json = '{"code":0,"msg":"success","data":{"current_page":1,"data":[{"id":374,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-07 14:46:19","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":380,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"2.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235915710\",\"batch_id\":54420,\"storage_manage_id\":42659,\"num\":\"2\"},{\"batch_no\":\"smyc20221107142359188888\",\"batch_id\":54420,\"storage_manage_id\":42659,\"num\":\"2\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"\u65b9\u5f0f\u5bf9\u65b9\u8fc7\u540e\u7684\u5feb\u901f\u653e\u6b3e\u540e","main_id":10317,"o_b_main_id":374,"materiel_no":"ELmE943R","created_at":"2022-11-07 14:46:19","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[{"id":27,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":374,"remark":"","created_at":"2022-11-07 14:46:19","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":42,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":27,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235960470\",\"batch_id\":53773,\"storage_manage_id\":42657,\"num\":\"1\"}]","o_b_id":374,"main_id":10317,"created_at":"2022-11-07 14:46:19","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":43,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":27,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110714235930920\",\"batch_id\":53788,\"storage_manage_id\":42651,\"num\":\"1\"}]","o_b_id":374,"main_id":10317,"created_at":"2022-11-07 14:46:19","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":349,"o_b_main_id":374,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 14:46:19","updated_at":null}]},{"id":369,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-07 13:51:57","updated_at":"2022-11-07 13:52:18","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":25,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":369,"remark":"","created_at":"2022-11-07 13:51:57","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":40,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":25,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":369,"main_id":10317,"created_at":"2022-11-07 13:51:57","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":41,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":25,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":369,"main_id":10317,"created_at":"2022-11-07 13:51:57","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":345,"o_b_main_id":369,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:51:57","updated_at":"2022-11-07 13:52:18"}]},{"id":368,"main_id":10317,"user_id":10317,"status":1,"proof_image":"\/uploads\/2022\/11\/07\/mqEzE0wjHRAldkYq3KQXS8uQyUPltTAoN3whO8GZ.jpg","type":1,"repeal_status":0,"created_at":"2022-11-07 13:50:21","updated_at":"2022-11-07 13:51:05","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":24,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":368,"remark":"\u624b\u673a\u5361\u8def\u4e0e\u6d6e\u4e91\u5934\u8bfa\u5973","created_at":"2022-11-07 13:50:21","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":38,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":24,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":368,"main_id":10317,"created_at":"2022-11-07 13:50:21","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":39,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":24,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":368,"main_id":10317,"created_at":"2022-11-07 13:50:21","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":344,"o_b_main_id":368,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:50:21","updated_at":"2022-11-07 13:51:05"}]},{"id":367,"main_id":10317,"user_id":10317,"status":2,"proof_image":"\/uploads\/2022\/11\/07\/m4fooTpKnSUmwTLaOYVLJQb56uJKJWBuTiYU5wig.jpg","type":1,"repeal_status":0,"created_at":"2022-11-07 13:49:38","updated_at":"2022-11-07 13:49:49","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[],"package_info":[{"id":23,"package_id":1002,"num":1,"main_id":10317,"o_b_main_id":367,"remark":"\u63d0\u524d\u4e86\u8fa3\u75bc\u61c2\u4e48\u7eaa\u59d4","created_at":"2022-11-07 13:49:38","updated_at":null,"package_name":"\u5957\u99101","package_content":[{"id":36,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":23,"storage_manage_id":42653,"type_id":48789,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":367,"main_id":10317,"created_at":"2022-11-07 13:49:38","updated_at":"1970-01-01 08:00:00","materiel_name":"\u9ed1\u8272\u7b7e\u5b57\u7b14","type_name":"\u9ed1\u8272","materiel_no":"3ZC6z6x1","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0},{"id":37,"package_id":1002,"num":"1.000000","total_num":"1.000000","o_b_p_id":23,"storage_manage_id":42651,"type_id":48786,"storage_id":5476,"shelf_id":4714,"shelf_location_id":4724,"serial_number":"","batch_info":"[]","o_b_id":367,"main_id":10317,"created_at":"2022-11-07 13:49:38","updated_at":"1970-01-01 08:00:00","materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","materiel_no":"ELmE943R","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}]}],"check_info":[{"id":343,"o_b_main_id":367,"status":2,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-07 13:49:38","updated_at":"2022-11-07 13:49:49"}]},{"id":356,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 17:22:00","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":365,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"},{\"batch_no\":\"smyc2022110416490188888\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":356,"materiel_no":"ELmE943R","created_at":"2022-11-04 17:22:00","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":332,"o_b_main_id":356,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 17:22:00","updated_at":null}]},{"id":353,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 16:50:55","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":362,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":353,"materiel_no":"ELmE943R","created_at":"2022-11-04 16:50:55","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":329,"o_b_main_id":353,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 16:50:55","updated_at":null}]},{"id":352,"main_id":10317,"user_id":10317,"status":0,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-11-04 16:49:25","updated_at":null,"username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":361,"materiel_type_id":48786,"name":"\u84dd\u8272\u7b7e\u5b57\u7b14","type_name":"\u84dd\u8272","num":"1.000000","image":"","warehouse":"\u4ed3\u5e931","warehouse_id":5476,"serial_number":"","batch_info":"[{\"batch_no\":\"smyc2022110416490180500\",\"batch_id\":53755,\"storage_manage_id\":42651,\"num\":\"1\"}]","shelf_id":4714,"shelf_location_id":4724,"remark":"","main_id":10317,"o_b_main_id":352,"materiel_no":"ELmE943R","created_at":"2022-11-04 16:49:25","updated_at":null,"materiel_name":"\u84dd\u8272\u7b7e\u5b57\u7b14","storage_name":"\u4ed3\u5e931","storage_address":"111","is_use_serial":0}],"package_info":[],"check_info":[{"id":328,"o_b_main_id":352,"status":0,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-11-04 16:49:25","updated_at":null}]},{"id":340,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 20:07:13","updated_at":"2022-10-29 20:07:21","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":349,"materiel_type_id":48336,"name":"\u94c5\u7b14","type_name":"\u957f","num":"1.000000","image":"","warehouse":"\u7761\u732b\u4e91\u4ed3","warehouse_id":5349,"serial_number":"202","batch_info":"","shelf_id":4588,"shelf_location_id":4596,"remark":"","main_id":10317,"o_b_main_id":340,"materiel_no":"oGjnQQp1","created_at":"2022-10-29 20:07:13","updated_at":null,"materiel_name":"\u94c5\u7b14","storage_name":"\u7761\u732b\u4e91\u4ed3","storage_address":"\u868c\u57e0","is_use_serial":1}],"package_info":[],"check_info":[{"id":316,"o_b_main_id":340,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 20:07:13","updated_at":"2022-10-29 20:07:21"}]},{"id":332,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 19:30:09","updated_at":"2022-10-29 19:31:09","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":341,"materiel_type_id":48334,"name":"\u7ea2\u5bcc\u58eb","type_name":"\u5927","num":"1.000000","image":"\/uploads\/2022\/10\/29\/4WKAGsbvGEX1291j1besvEgxVeekQ73DudMAJ0ar.png","warehouse":"\u4e00\u4ed3\u5e93","warehouse_id":5350,"serial_number":"","batch_info":"[]","shelf_id":1,"shelf_location_id":1,"remark":"\u63a8\u9001\u7684\u8d35\u91d1\u5c5e\u7684\u7a7a\u95f4\u5206\u5272","main_id":10317,"o_b_main_id":332,"materiel_no":"3ilE6S02","created_at":"2022-10-29 19:30:09","updated_at":null,"materiel_name":"\u7ea2\u5bcc\u58eb","storage_name":"\u4e00\u4ed3\u5e93","storage_address":"","is_use_serial":0}],"package_info":[],"check_info":[{"id":308,"o_b_main_id":332,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 19:30:09","updated_at":"2022-10-29 19:31:09"}]},{"id":329,"main_id":10317,"user_id":10317,"status":1,"proof_image":null,"type":1,"repeal_status":0,"created_at":"2022-10-29 19:23:02","updated_at":"2022-10-29 19:23:12","username":"13696533075","nickname":"\u8bbf\u5ba2","goods_info":[{"id":339,"materiel_type_id":48352,"name":"\u8461\u8404","type_name":"\u8461\u8404","num":"1.000000","image":"","warehouse":"\u4e00\u4ed3\u5e93","warehouse_id":5350,"serial_number":"","batch_info":"[]","shelf_id":1,"shelf_location_id":1,"remark":"","main_id":10317,"o_b_main_id":329,"materiel_no":"c3T5gc4K","created_at":"2022-10-29 19:23:02","updated_at":null,"materiel_name":"\u8461\u8404","storage_name":"\u4e00\u4ed3\u5e93","storage_address":"","is_use_serial":0}],"package_info":[],"check_info":[{"id":306,"o_b_main_id":329,"status":1,"name":"\u8bbf\u5ba2","mobile":"13696533075","admin_id":10317,"refund_remark":null,"created_at":"2022-10-29 19:23:02","updated_at":"2022-10-29 19:23:12"}]}],"first_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=1","from":1,"last_page":2,"last_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","links":[{"url":null,"label":"pagination.previous","active":false},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=1","label":"1","active":true},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","label":"2","active":false},{"url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","label":"pagination.next","active":false}],"next_page_url":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list?=2","path":"https:\/\/new.storages.top\/v3.1\/api\/overflow_breakage_list","per_page":"10","prev_page_url":null,"to":10,"total":17}}';
       $arr  = json_decode($json,true);

       $exportData = $arr['data']['data']; */
      /*  echo "<pre>";
       var_dump($exportData);die; */

       $title = !empty($data['type'])?"报损":"报溢";

       @ini_set('memory_limit', '1024M');
       @ini_set('max_execution_time', 30);
       ob_end_clean();
       ob_start();
       $spreadsheet = new Spreadsheet();
       $sheet = $spreadsheet->getActiveSheet();
       $spreadsheet->getDefaultStyle();
       $sheet->setTitle($title."记录");
       //A1-L1字体加粗
       $spreadsheet->getActiveSheet()->getStyle('A1:Z1')->getFont()->setBold(true);
       //设置字体
       $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
       //设置字体大小
       $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
       //设置默认行高
       $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);
       $spreadsheet->getActiveSheet()->getStyle('A:Z')->getAlignment()->setHorizontal('center');
       $spreadsheet->getActiveSheet()->getStyle('A:Z')->getAlignment()->setVertical('center');
       $letterArray = [];
        for ($i = 65; $i < 91; $i++) {
            $letterArray[] = chr($i); // 直接输出大写字母
            $spreadsheet->getActiveSheet()->getColumnDimension(chr($i))->setWidth(30);
            //设置自动换行
            $spreadsheet->getActiveSheet()->getStyle(chr($i))->getAlignment()->setWrapText(true);
        }
       $sheet  -> setCellValue('A1', '操作人')
               -> setCellValue('B1', '操作账号')
               -> setCellValue('C1', '审批状态')
               -> setCellValue('D1', '操作状态')
               -> setCellValue('E1', '创建时间')
               -> setCellValue('F1', '商品信息')
               -> setCellValue('G1', '名称（规格）')
               -> setCellValue('H1', '仓库名称')
               -> setCellValue('I1', '仓库地址')
               -> setCellValue('J1', '序列号')
               -> setCellValue('K1', '批次号')
               -> setCellValue('L1', '商品数量')
               -> setCellValue('M1', '商品描述')
               -> setCellValue('N1', '套餐信息')
               -> setCellValue('O1', '套餐名称')
               -> setCellValue('P1', '套餐数量')
               -> setCellValue('Q1', '套餐描述')
               -> setCellValue('R1', '商品名称')
               -> setCellValue('S1', '仓库名称')
               -> setCellValue('T1', '仓库地址')
               -> setCellValue('U1', '序列号')
               -> setCellValue('V1', '批次号')
               -> setCellValue('W1', '审核信息')
               -> setCellValue('X1', '审核人')
               -> setCellValue('Y1', '手机号')
               -> setCellValue('Z1', '审核备注');

       foreach ($exportData as $key=>$value){

           switch ($value['status']){
               case 2:$status="拒绝";break;
               case 1:$status="通过";break;
               case 0:$status="待审批";break;
           }

           if($value['repeal_status']){
               $repeal_status="已撤销";
           }else{
               $repeal_status="未撤销";
           }
           if(!empty($value['goods_info']) && empty($value['package_info'])){//只有商品没有套餐商品
               $totalNum[] = count($value['goods_info']);
               foreach ($value['goods_info'] as $gk=>$gv){
                   $list[] = [
                       'nickname'           => $value['nickname'],
                       'username'           => $value['username'],
                       'status'             => $status,
                       'repeal_status'      => $repeal_status,
                       'created_at'         => $value['created_at'],
                       'goods_info'         => "商品信息>>>",
                       'goods_name'               => $gv['name'].'/'.$gv['type_name'].'（'.$gv['materiel_no'].'）',
                       'warehouse'          => $gv['warehouse'],
                       'storage_address'    => $gv['storage_address'],
                       'serial_number'      => $gv['serial_number'],
                       'batch_info'         => $gv['batch_info'],
                       'num'                => $gv['num'],
                       'remark'             => $gv['remark'],
                       'tc_info'            => "套餐信息>>>",
                       'package_name'       => "",
                       'tc_num'             => "",
                       'tc_remark'          => "",
                       'tc_materiel_name'   => "",
                       'tc_storage_name'    => "",
                       'tc_storage_address' => "",
                       'tc_serial_number'   => "",
                       'tc_batch_info'      => "",
                       'check_info'         => "审核信息>>>",
                       'name'               => !empty($value['check_info'][$gk]['name'])?$value['check_info'][$gk]['name']:"",
                       'mobile'             => !empty($value['check_info'][$gk]['mobile'])?$value['check_info'][$gk]['mobile']:"",
                       'refund_remark'      => !empty($value['check_info'][$gk]['mobile'])?$value['check_info'][$gk]['refund_remark']:""
                   ];
               }
           }else{

               foreach ($value['package_info'] as $pk => $pv){
                   if(count($pv['package_content']) >= count($value['goods_info'])){//套餐商品数量大于等于商品数量
                       $totalNum[] = count($pv['package_content']);
                       foreach ($pv['package_content'] as $ck => $cv){
                           if(!empty($value['goods_info'][$ck])){
                           $goodsName = $value['goods_info'][$ck]['name'].'/'.$value['goods_info'][$ck]['type_name'].'（'.$value['goods_info'][$ck]['materiel_no'].'）';
                           }
                           $tcGoodsName = $cv['materiel_name'].'/'.$cv['type_name'].'（'.$cv['materiel_no'].'）';
                           $list[] = [
                               'nickname'           => $value['nickname'],
                               'username'           => $value['username'],
                               'status'             => $status,
                               'repeal_status'      => $repeal_status,
                               'created_at'         => $value['created_at'],
                               'goods_info'         => "商品信息>>>",
                               'goods_name'               => !empty($goodsName)?$goodsName:"",
                               'warehouse'          => !empty($value['goods_info'][$ck]['warehouse'])?$value['goods_info'][$ck]['warehouse']:"",
                               'storage_address'    => !empty($value['goods_info'][$ck]['storage_address'])?$value['goods_info'][$ck]['storage_address']:"",
                               'serial_number'      => !empty($value['goods_info'][$ck]['serial_number'])?$value['goods_info'][$ck]['serial_number']:"",
                               'batch_info'         => !empty($value['goods_info'][$ck]['batch_info'])?$value['goods_info'][$ck]['batch_info']:"",
                               'num'                => !empty($value['goods_info'][$ck]['num'])?$value['goods_info'][$ck]['num']:"",
                               'remark'             => !empty($value['goods_info'][$ck]['remark'])?$value['goods_info'][$ck]['remark']:"",
                               'tc_info'            => "套餐信息>>>",
                               'package_name'       => $pv['package_name'],
                               'tc_num'             => $pv['num'],
                               'tc_remark'          => $pv['remark'],
                               'tc_materiel_name'   => $tcGoodsName,
                               'tc_storage_name'    => $cv['storage_name'],
                               'tc_storage_address' => $cv['storage_address'],
                               'tc_serial_number'   => $cv['serial_number'],
                               'tc_batch_info'      => $cv['batch_info'],
                               'check_info'         => "审核信息>>>",
                               'name'               => !empty($value['check_info'][$ck]['name'])?$value['check_info'][$ck]['name']:"",
                               'mobile'             => !empty($value['check_info'][$ck]['mobile'])?$value['check_info'][$ck]['mobile']:"",
                               'refund_remark'      => !empty($value['check_info'][$ck]['mobile'])?$value['check_info'][$ck]['refund_remark']:"",
                           ];
                       }
                   }else{
                       $totalNum[] = count($value['goods_info']);
                       foreach ($pv['goods_info'] as $gk=>$gv){//商品数量大于套餐商品数量
                           $goodsName = $gv['name'].'/'.$gv['type_name'].'（'.$gv['materiel_no'].'）';
                           if(!empty($pv['package_content'][$gk])){
                               $tcGoodsName = $pv['package_content'][$gk]['materiel_name'].'/'.$pv['package_content'][$gk]['type_name'].'（'.$pv['package_content'][$gk]['materiel_no'].'）';
                           }
                           $list[] = [
                               'nickname'           => $value['nickname'],
                               'username'           => $value['username'],
                               'status'             => $status,
                               'repeal_status'      => $repeal_status,
                               'created_at'         => $value['created_at'],
                               'goods_info'         => "商品信息>>>",
                               'goods_name'               => $goodsName,
                               'warehouse'          => $gv['warehouse'],
                               'storage_address'    => $gv['storage_address'],
                               'serial_number'      => $gv['serial_number'],
                               'batch_info'         => $gv['batch_info'],
                               'num'                => $gv['num'],
                               'remark'             => $gv['remark'],
                               'tc_info'            => "套餐信息>>>",
                               'package_name'       => !empty($pv['package_name'])?$pv['package_name']:"",
                               'tc_num'             => !empty($pv['num'])?$pv['num']:"",
                               'tc_remark'          => !empty($pv['remark'])?$pv['remark']:"",
                               'tc_materiel_name'   => !empty($tcGoodsName)?$tcGoodsName:"",
                               'tc_storage_name'    => !empty($pv['package_content'][$gk]['storage_name'])?$pv['package_content'][$gk]['storage_name']:"",
                               'tc_storage_address' => !empty($pv['package_content'][$gk]['storage_address'])?$pv['package_content'][$gk]['storage_address']:"",
                               'tc_serial_number'   => !empty($pv['package_content'][$gk]['serial_number'])?$pv['package_content'][$gk]['serial_number']:"",
                               'tc_batch_info'      => !empty($pv['package_content'][$gk]['batch_info'])?$pv['package_content'][$gk]['batch_info']:"",
                               'check_info'         => "审核信息>>>",
                               'name'               => !empty($value['check_info'][$gk]['name'])?$value['check_info'][$gk]['name']:"",
                               'mobile'             => !empty($value['check_info'][$gk]['mobile'])?$value['check_info'][$gk]['mobile']:"",
                               'refund_remark'      => !empty($value['check_info'][$gk]['mobile'])?$value['check_info'][$gk]['refund_remark']:"",
                           ];
                       }
                   }
               }
           }
       }

       $cell = ['A','B','C','D','E','F','N','O','P','Q','W'];//需要合并的单元格

       if(!empty($list)){
           foreach ($cell as $item) {
               $start = 2;
               foreach ($totalNum as $key => $value) {
                   $end = ($start + $value)-1;
                   $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                   $start = $end + 1;
               }
           }
       }

       if(!empty($list)){
           foreach($list as $key => $value) {
               $batch_no = "";
               $tc_batch_no = "";
               $value['batch_info']    = json_decode($value['batch_info'],true);
               $value['tc_batch_info'] = json_decode($value['tc_batch_info'],true);
               if(!empty($value['batch_info'])){
                   foreach ($value['batch_info'] as $bk => $bv){
                       $batch_no .= $bv['batch_no'].',';
                   }
                   $batch_no = substr($batch_no,0,strrpos($batch_no,","));
               }

               if(!empty($value['tc_batch_info'])){
                   foreach ($value['tc_batch_info'] as $tk=>$tv){
                       $tc_batch_no .= $tv['batch_no'].',';
                   }

                   $tc_batch_no = substr($tc_batch_no,0,strrpos($tc_batch_no,","));
               }
               $key += 2;
               $sheet->setCellValue('A'.$key, $value['nickname'])
               ->setCellValue('B'.$key, $value['username'])
               ->setCellValue('C'.$key, $value['status'])
               ->setCellValue('D'.$key, $value['repeal_status'])
               ->setCellValue('E'.$key, $value['created_at'])
               //商品信息
               ->setCellValue('F'.$key, $value['goods_info'])
               ->setCellValue('G'.$key, $value['goods_name'])
               ->setCellValue('H'.$key, $value['warehouse'])
               ->setCellValue('I'.$key, $value['storage_address'])
               ->setCellValue('J'.$key, $value['serial_number'])
               ->setCellValue('K'.$key, $batch_no)
               ->setCellValue('L'.$key, $value['num'])
               ->setCellValue('M'.$key, $value['remark'])
               //套餐商品信息
               ->setCellValue('N'.$key, $value['tc_info'])
               ->setCellValue('O'.$key, $value['package_name'])
               ->setCellValue('P'.$key, $value['tc_num'])
               ->setCellValue('Q'.$key, $value['tc_remark'])
               ->setCellValue('R'.$key, $value['tc_materiel_name'])
               ->setCellValue('S'.$key, $value['tc_storage_name'])
               ->setCellValue('T'.$key, $value['tc_storage_address'])
               ->setCellValue('U'.$key, $value['tc_serial_number'])
               ->setCellValue('V'.$key, $tc_batch_no)
               //审核人信息
               ->setCellValue('W'.$key, $value['check_info'])
               ->setCellValue('X'.$key, $value['name'])
               ->setCellValue('Y'.$key, $value['mobile'])
               ->setCellValue('Z'.$key, $value['refund_remark']);
           }
       }
       return self::downloadExcel($spreadsheet, $title.'记录表_'.date('Y-m-d'), 'Xlsx');
   }

    static public function costStatementHandle($title,$data){

        if(!empty($data['start_time'])) {
            $startTimeArr = explode(' ', $data['start_time']);
            $startTime = strtotime($startTimeArr[0].' 00:00:00');
        }

        if(!empty($data['end_time'])) {
            $endTimeArr = explode(' ', $data['end_time']);
            $endTime = strtotime($endTimeArr[0].' 23:59:59');
        }

        $condition = [];
        if(empty($data['token'])) {

            return ['code'=>1,'msg'=>'token验证失败，请重新登录','data'=>''];
        }
        $array = explode(' ', $data['token']);

        $adminInfo = AdminModel::where('id', $array[0])->first();

        if(empty($adminInfo)) return ['code'=>1,'msg'=>'导出异常','data'=>''];

        if($adminInfo['id'] == $adminInfo['parent_id']){
            $condition['a.main_id'] = ['EQ' => $adminInfo['parent_id']];
        }else {
            $condition['a.user_id'] = ['EQ' => $adminInfo['id']];
        }

        if(!empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.out_storage_time', '>=', $startTime];
            $condition[] = ['a.out_storage_time', '<=', $endTime];
        }
        else if(!empty($startTime) && empty($endTime)) {
            $condition[] = ['a.out_storage_time', '>=', $startTime];
        }
        else if(empty($startTime) && !empty($endTime)) {
            $condition[] = ['a.out_storage_time', '<=', $endTime];
        }

        $exportData = StatisticsService::statisticsGoodsSoldExport($condition, $data);
        /* echo "<pre>";
         var_dump($exportData);die; */

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle($title);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:I')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);

        $sheet->setCellValue('A1', '商品名称（规格）')
        ->setCellValue('B1', '数量')
        ->setCellValue('C1', '售价（元）')
        ->setCellValue('D1', '成本（元）')
        ->setCellValue('E1', '利润（元）')
        ->setCellValue('F1', '总数量')
        ->setCellValue('G1', '总成本')
        ->setCellValue('H1', '总售价（元）')
        ->setCellValue('I1', '总利润（元）');
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];

        $materielLine[] = count($exportData['sell_materiel_info']);

        foreach($exportData['sell_materiel_info'] as $val) {

            if($data['type'] ==1){
                $name=$val['package_name'];
                $cost=$exportData['total_price'];
            }else{
                $name=$val['materiel_name'].'/'.$val['type_name'].'（'.$val['materiel_no'].'）';
                $cost=$exportData['total_cost'];
            }
            $list[] = [
                'materiel_name' => $name,
                'total_num' => $val['total_num'],
                'total_price' => $val['total_price'],
                'total_cost' => $val['total_cost'],
                'total_income' => $val['total_price']-$val['total_cost'],
                'total_amount' => $exportData['total_amount'],
                'z_total_cost' => $cost,
                'total_sell_price' => $exportData['total_sell_price'],
                'z_total_income' => $exportData['total_income']
            ];
        }

        $cell = ['F','G','H','I'];//需要合并的单元格
        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($materielLine as $k => $value) {
                    $end = ($start + $value)-1;
                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }
            }
        }
        if(!empty($list)){
            foreach($list as $key => $value) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $value['materiel_name'])
                ->setCellValue('B'.$key, $value['total_num'])
                ->setCellValue('C'.$key, $value['total_price'])
                ->setCellValue('D'.$key, $value['total_cost'])
                ->setCellValue('E'.$key, $value['total_income'])
                ->setCellValue('F'.$key, $value['total_amount'])
                ->setCellValue('G'.$key, $value['z_total_cost'])
                ->setCellValue('H'.$key, $value['total_sell_price'])
                ->setCellValue('I'.$key, $value['z_total_income']);
            }
        }
        return SELF::downloadExcel($spreadsheet, $title.'_'.date('Y-m-d'), 'Xlsx');

    }

    //实现对excel的下载
    private static function downloadExcel($spreadsheet, $filename, $format,$imgArr=[])
    {
        if($format == 'Xlsx') {
            $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        }
        else if($format == 'Xls') {
            $contentType = 'application/vnd.ms-excel';
        }

        $filenameUrl = $filename.'.'.strtolower($format);
        $objWriter = IOFactory::createWriter($spreadsheet, $format);
        //保存到服务器临时文件下
        $objWriter->save('./tmp.xlsx');
        //删除临时文件
        unlink('./tmp.xlsx');

        header('Content-Type: '.$contentType.'; charset=utf-8');    //输出文件类型
        header('Content-Disposition: attachment;filename="'.$filenameUrl.'"');    //输出文件名
        header('Cache-Control: max-age=0');    //强制每次请求直接发送给源服务器，而不经过本地缓存版本的校验
        header('Access-Control-Allow-Origin:*');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //清空
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        if(!empty($imgArr)){
            foreach ($imgArr as $iVal){
                unlink($iVal);
            }
        }
        exit();
    }

    public function materielExport(Request $request)
    {
        //参数验证
        $validator = Validator::make($data = $request->all(), [
            'search_type'=>[
                'required',
                Rule::In([0,1,2,3,4,5,6,7,8])
            ],
            'status'=>[
                'required',
                Rule::In([0,1,2,3,4,5,6])
            ]
        ],[
            'search_type.required' => '参数`搜索类型`缺失',
            'search_type.in' => '参数`搜索类型`不合法',
            'status.required' => '参数`状态`缺失',
            'status.in' => '参数`状态`不合法',
        ]);

        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => ''
            ]);
        }

        $token = $request->header('token');
        $result = self::checkToken($token);

        $adminInfo = AdminModel::where('id', $result['userId'])->first();
        if($adminInfo['parent_id'] != 0) {
            $parentId = $adminInfo['parent_id'];
        }
        else {
            $parentId = $result['userId'];
        }

        $materielData = MaterielsService::materielExport($data, $parentId);

        self::export($materielData);

        return true;
    }

    /**
     * 采购单导出代码
     * @param $materielData
     * 2022/10/21 14:46
     * Author: Sun Wu
     * @return bool
     */
    protected function export($materielData)
    {
        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('采购单报表导出');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:T1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:T')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:T')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(20);

        $sheet->setCellValue('A1', '供应商名')
            ->setCellValue('B1', '采购日期')
            ->setCellValue('C1', '优惠方式')
            ->setCellValue('D1', '实付金额')
            ->setCellValue('E1', '商品名')
            ->setCellValue('F1', '商品规格')
            ->setCellValue('G1', '商品图')
            ->setCellValue('H1', '单价')
            ->setCellValue('I1', '采购数量')
            ->setCellValue('J1', '序列号')
            ->setCellValue('K1', '仓库名')
            ->setCellValue('L1', '货架')
            ->setCellValue('M1', '库位')
            ->setCellValue('N1', '发货方式')
            ->setCellValue('O1', '运输方式')
            ->setCellValue('P1', '物流公司')
            ->setCellValue('Q1', '物流单号')
            ->setCellValue('R1', '发货人')
            ->setCellValue('S1', '发货人联系方式')
            ->setCellValue('T1', '备注');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setWrapText(true);

        $materielLine = [];
        $list = [];
        foreach($materielData as $value) {
            $materielLine[] = count($value['materiel_content']);

            foreach($value['materiel_content'] as $val) {
                $list[] = [
                    'supplier_name' => $value['supplier_name'],
                    'purchase_time' => $value['purchase_time'],
                    'preferential_way' => $value['preferential_way'],
                    'final_money' => $value['final_money'],
                    'materiel_name' => $val['materiel_name'],
                    'type_name' => $val['type_name'],
                    'materiel_img' => $val['materiel_img'],
                    'unit_price' => $val['unit_price'],
                    'num' => $val['num'],
                    'serial_no' => $val['serial_no'],
                    'storage_name' => $val['storage_name'],
                    'shelf_name' => $val['shelf_name'],
                    'location_name' => $val['location_name'],
                    'shipping_method' => $value['shipping_method'],
                    'transport_type' => $value['transport_type'],
                    'logistics_company' => empty($value['logistics_company']) ? '' : $value['logistics_company'],
                    'logistics_order_no' => empty($value['logistics_order_no']) ? '' : $value['logistics_order_no'],
                    'deliver_person' => empty($value['deliver_person']) ? '' : $value['deliver_person'],
                    'deliver_phone' => empty($value['deliver_phone']) ? '' : $value['deliver_phone'],
                    'remark' => empty($value['remark']) ? '' : $value['remark'],
                ];

            }
        }

        $cell = ['A', 'B', 'C', 'D', 'N', 'O', 'P', 'Q', 'R', 'S','T','U','V','W','X','Y','AI','AJ','AK','AL','AM','AN'];//需要合并的单元格

        foreach ($cell as $item) {
            $start = 2;
            foreach ($materielLine as $k => $value) {
                $end = ($start + $value)-1;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                $start = $end + 1;
            }
        }

        if(!empty($list)) {
            foreach($list as $key => $materielData) {
                $key += 2;
                $sheet->setCellValue('A'.$key, $materielData['supplier_name'])
                    ->setCellValue('B'.$key, $materielData['purchase_time']);

                switch($materielData['preferential_way']) {
                    case 0:
                        $sheet->setCellValue('C'.$key, '无');
                        break;
                    case 1:
                        $sheet->setCellValue('C'.$key, '修改价格');
                        break;
                    case 2:
                        $sheet->setCellValue('C'.$key, '折扣优惠');
                        break;
                }

                $sheet->setCellValue('D'.$key, $materielData['final_money'])
                    ->setCellValue('E'.$key, $materielData['materiel_name'])
                    ->setCellValue('F'.$key, $materielData['type_name'])
                    ->setCellValue('H'.$key, $materielData['unit_price'])
                    ->setCellValue('I'.$key, $materielData['num'])
                    ->setCellValue('J'.$key, $materielData['serial_no'])
                    ->setCellValue('K'.$key, $materielData['storage_name'])
                    ->setCellValue('L'.$key, $materielData['shelf_name'])
                    ->setCellValue('M'.$key, $materielData['location_name']);

                switch($materielData['shipping_method']) {
                    case 1:
                        $sheet->setCellValue('N'.$key, '自提');
                        break;
                    case 2:
                        $sheet->setCellValue('N'.$key, '代发');
                        break;
                    case 3:
                        $sheet->setCellValue('N'.$key, '送货');
                        break;
                }

                switch($materielData['transport_type']) {
                    case 1:
                        $sheet->setCellValue('O'.$key, '空运');
                        break;
                    case 2:
                        $sheet->setCellValue('O'.$key, '海运');
                        break;
                    case 3:
                        $sheet->setCellValue('O'.$key, '铁路');
                        break;
                    case 4:
                        $sheet->setCellValue('O'.$key, '公路');
                        break;
                }

                if(!empty($materielData['materiel_img'])) {
                    $imgPath = config('app.aliyun_oss_link').$materielData['materiel_img'];
                    $sheet->setCellValue('G'.$key, $imgPath);
                    $client = $this->ossClient;
                    $ossPath = ltrim($materielData['materiel_img'], './');
                    $exist = $client->doesObjectExist(config('app.aliyun_oss_bucket'), $ossPath);

                    if(!$exist) {
                        $sheet->setCellValue('G'.$key, '');
                    }
                    else {
                        $fileInfo = pathinfo($imgPath);
                        $sheet->setCellValue('G'.$key, '');
                        //过滤非文件类型
//                        if(!empty($fileInfo['basename'])) {
//                            $drawing[$key] = new Drawing();
//                            $drawing[$key]->setResizeProportional(false);
//                            $drawing[$key]->setName('商品图');
//                            $drawing[$key]->setDescription('商品图');
//                            $drawing[$key]->setPath('');
////                            $drawing[$key]->setPath($imgPath);
//                            $drawing[$key]->setWidth(40);
//                            $drawing[$key]->setHeight(40);
//                            $drawing[$key]->setOffsetX(10);
//                            $drawing[$key]->setOffsetY(10);
//                            $drawing[$key]->setCoordinates('G'.$key);
//                            $drawing[$key]->setWorksheet($sheet);
//                        }
                    }
                }
                else {
                    $sheet->setCellValue('G'.$key, '');
                }

                $sheet->setCellValue('P'.$key, empty($materielData['logistics_company'])?'':$materielData['logistics_company']);
                $sheet->setCellValue('Q'.$key, empty($materielData['logistics_order_no'])?'':$materielData['logistics_order_no']);
                $sheet->setCellValue('R'.$key, empty($materielData['deliver_person'])?'':$materielData['deliver_person']);
                $sheet->setCellValue('S'.$key, empty($materielData['deliver_phone'])?'':$materielData['deliver_phone']);
                $sheet->setCellValue('T'.$key, empty($materielData['remark'])?'':$materielData['remark']);
            }
        }

        ob_end_clean();

        self::downloadExcel($spreadsheet, '采购单报表_'.date('Y-m-d'), 'Xlsx');
        return true;
    }

    /**
     * 导出excel-库存
     * @param Request $request
     * AUTHOR  : Sun Wu
     * DateTime: 2022/7/30 11:34
     * @return array|void
     */
    public function storageManageExport(Request $request)
    {
        $token = $request->header('token');
        $tokenInfo = self::checkToken($token);
        if(!$tokenInfo) {
            return $tokenInfo;
        }

        $user = [];
        if(!$tokenInfo['userType']) {
            $user['a.main_id'] = $tokenInfo['mainUserId'];
        }
        else {
            $user['a.user_id'] = $tokenInfo['userId'];
        }
        //$user['a.main_id'] = 6588;
        /* $user['a.main_id'] = 16192;
        $tokenInfo['mainUserId'] = 16192;
        $tokenInfo['userId']=16192; */
        $data = $request->all();
       
        $mainInfo = AdminAddendumModel::where('main_id', $tokenInfo['mainUserId'])->first();

        if($tokenInfo['mainUserId'] == $tokenInfo['userId']) {
            $mainId['storage_manage.main_id'] = ['EQ' => $tokenInfo['mainUserId']];
        }
        else {
            if($mainInfo['is_share_store']) {
                $mainId['storage_manage.main_id'] = ['EQ' => $tokenInfo['mainUserId']];
            }
            else {
                $mainId['e.user_id'] = ['EQ' => $tokenInfo['userId']];
            }
        }

        $data['order_type'] = empty($data['order_type'])?0:$data['order_type'];

        $data['order_by'] = empty($data['order_by']) ?'asc':$data['order_by'];

        if(!in_array($data['order_by'], ['desc','asc'])) {
            return response()->json([
                'code' => 1,
                'msg' => '不支持的排序方式',
                'data' => ''
            ]);
        }

        //获取导出的数据
        $exportData = CommonStorageOperateService::exportData($mainId, $data);

        if(empty($exportData)) {
            return response()->json([
                'code' => 1,
                'msg' => '暂无可导出库存数据',
                'data' => ''
            ]);
        }

//        if(empty($exportData)) {
//            return response()->json(['code' => 1,'msg' => '无可用数据可供导出','data' => '']);
//        }

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 60);
        set_time_limit(60);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('库存导出报表');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:R1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:R')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:R')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(25);

        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '编号/货号')
            ->setCellValue('C1', '商品名')
            ->setCellValue('D1', '型号/规格')
            ->setCellValue('E1', '仓库名')
            ->setCellValue('F1', '一级分类')
            ->setCellValue('G1', '二级分类')
            ->setCellValue('H1', '单位')
            ->setCellValue('I1', '条形码')
            ->setCellValue('J1', '销售价')
            ->setCellValue('K1', '最低销售价')
            ->setCellValue('L1', '成本价')
            ->setCellValue('M1', '库存数')
            ->setCellValue('N1', '备注')
            ->setCellValue('O1', '商品/套餐;0:商品:1:套餐;')
            ->setCellValue('P1', '未使用序列号')
            ->setCellValue('Q1', '已使用序列号');

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);

        $singleGoodsCostArr = [];
        $totalInStorageNumArr = [];
        $key = 0;
        $imgPathArr = [];
        foreach($exportData as $key => $value) {
            $key += 2;
            $serialList = "";
            if(!empty($value['materiel_img'])) {
                
            $sheet->setCellValue('A'.$key, '');
            /* $localpath = public_path($value['materiel_img']);
            if(!file_exists($localpath)) {
                $uploadPath = date('Y').'/'.date('m').'/'.date('d');
                $path = public_path('/uploads/'.$uploadPath);
                $filePpath = strstr($value['materiel_img'], 'uploads/');
                $localpath = $path.'/'.time().rand(1000,9999).$value['id'].'.png';
                $options = array(
                    $this->ossClient::OSS_FILE_DOWNLOAD => $localpath
                );
                if (!file_exists($path)) {
                    @mkdir($path, 0777, true);
                }
                try {
                    $imgPath = $this->ossClient->getObject(config('app.aliyun_oss_bucket'), $filePpath,$options);
                    $imgPathArr[] = $localpath;
                } catch (\Exception $e) {
                }
            }
            $fileInfo = pathinfo($localpath);
            //过滤非文件类型
            if(!empty($fileInfo['basename'])) {
                $drawing[$key] = new Drawing();
                $drawing[$key]->setResizeProportional(false);
                $drawing[$key]->setName('商品图');
                $drawing[$key]->setDescription('商品图');
                $drawing[$key]->setPath($localpath);
                $drawing[$key]->setWidth(50);
                $drawing[$key]->setHeight(50);
                $drawing[$key]->setOffsetX(10);
                $drawing[$key]->setOffsetY(10);
                $drawing[$key]->setCoordinates('A'.$key);
                $drawing[$key]->setWorksheet($sheet);
            } */
        } else {
                $sheet->setCellValue('A'.$key, '');
            }

            $serialData = [];
            $usedSerialData = [];

            if($value['is_use_serial'] == 1){
                $serialData = SerialNumberModel::select('serial_no')->where([
                    'storage_manage_id'=>$value['id'],
                    'goods_id'=>$value['type_id'],
                    'status'=>1
                ])->pluck('serial_no')->toArray();
                //已使用序列号
                $usedSerialData = SerialNumberModel::select('serial_no')->where([
                    'storage_manage_id'=>$value['id'],
                    'goods_id'=>$value['type_id'],
                    'status'=>0
                ])->pluck('serial_no')->toArray();
            }

            $singleGoodsCostArr[] = $value['materiel_mean_cost'] * $value['in_storage_num'];
            $totalInStorageNumArr[] = $value['in_storage_num'];

            $sheet->setCellValue('B'.$key, $value['materiel_no'])
                ->setCellValue('C'.$key, $value['materiel_name'])
                ->setCellValue('D'.$key, $value['type_name'])
                ->setCellValue('E'.$key, $value['storage_name'])
                ->setCellValue('F'.$key, $value['f_class_name'])
                ->setCellValue('G'.$key, $value['s_class_name'])
                ->setCellValue('H'.$key, $value['materiel_unit'])
                ->setCellValue('I'.$key, $value['bar_code'])
                ->setCellValue('J'.$key, $value['selling_price'])
                ->setCellValue('K'.$key, $value['lower_selling_price'])
                ->setCellValue('L'.$key, $value['materiel_mean_cost'])
                ->setCellValue('M'.$key, $value['in_storage_num'])
                ->setCellValue('N'.$key, $value['remark'])
                ->setCellValue('O'.$key, $value['is_product_materiel'])
                ->setCellValue('P'.$key, implode(',',$serialData))
                ->setCellValue('Q'.$key, implode(',',$usedSerialData));
        }

        $singleGoodsCost = 0;
        foreach($singleGoodsCostArr as $val) {
            $singleGoodsCost += sprintf('%.6f',$val);
        }

        $totalInStorageNum = 0;
        foreach($totalInStorageNumArr as $val) {
            $totalInStorageNum += sprintf('%.6f',$val);
        }

        $sheet->setCellValue('R1', '总成本:'.$singleGoodsCost)
            ->setCellValue('R2', '总库存:'.$totalInStorageNum);
        $spreadsheet->getActiveSheet()->getStyle('Q2')->getAlignment()->setWrapText(true);

        return SELF::downloadExcel($spreadsheet, '库存报表_'.date('Y-m-d'), 'Xlsx',$imgPathArr);
        
    }

    static private function checkToken($token)
    {
        if(empty($token)){
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        $array = explode(' ', $token);
        //私钥获取
        $private = SystemConfigModel::getConfigValue('private');

        if(count($array)<2){
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        //解密加密参数并验证
        $rsadata = DBRSAUtil::rsaDecrypt($array[1], $private);
        $rsadata = json_decode($rsadata,true);

        if($rsadata['userId'] != $array['0']){
            return [
                'code' => 2,
                'msg' => 'token验证失败',
                'data' => '',
            ];
        }

        return $rsadata;
    }

    public function storageStartExport(Request $request)
    {
        $data = $request->all();

        $data['materiel_class_id'] = empty($data['materiel_class_id'])?0:$data['materiel_class_id'];
        //撤销状态;0:未撤销;1:已撤销;
        $data['status'] = empty($data['status'])?0:$data['status'];
        $data['type'] = empty($data['type'])?0:$data['type'];//操作日志类型;0:入库;1:出库;
        $data['log_type'] = empty($data['log_type'])?0:$data['log_type'];
//        $token = '9605 pURltKpZ5PQc7Gr9m992mq1Atjcrn57pSqlOjGC9E9IKbiK8o7dH7uvq15ip1r5c7gydmkYI1BGnW9Nf0eAldmCf/qAuU5vN0PRfokZwphhFVq889ucjGg5m/3tcHaj4ZI+v8eQC2B+n9oKjYInuMVZlXUkyRY35xidMRNSqJ7UFclMXsrTDoWI4LAzJK/QrZ0UeqnLO24TvgbxOOtXL/qB8JCUoUV2XBcWTwKoYsZwL+ajH7/7AyZEXkjTVouKXzC6eh+7onoqbGO6JJ8Bo6mGHf5lwHErXqzGoUSNEKj5siN9fcvioosfUJfRJpVuWV+khniB41At3LP9EDNdp5A==';
        $token = $request->header('token');
        $userInfo = self::checkToken($token);
        $data['user_id'] = $userInfo['userId'];
        $data['label_id'] = empty($data['label_id'])?'':$data['label_id'];
        $data['main_id'] = $userInfo['mainUserId'];
        $mainInfo = AdminAddendumModel::where('main_id', $data['main_id'])->first();
        $data['is_share_log'] = $mainInfo['is_share_operating_record'];

        @ini_set('memory_limit', '1024M');
        @set_time_limit(0);
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('出入库记录报表');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:N1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $keywordCondition = [];
        if(!empty($data['keyword'])) {
            switch($data['keyword_type']) {
                case 0://商品名
                    $keywordCondition[] = ['e.materiel_name', 'like', '%'.$data['keyword'].'%'];
                    break;
                case 1://型号名
                    $keywordCondition[] = ['e.type_name', 'like', '%'.$data['keyword'].'%'];
                    break;
                case 2://商品编号
                    $keywordCondition[] = ['e.materiel_no', 'like', '%'.$data['keyword'].'%'];
                    break;
                case 3://仓库名
                    $keywordCondition[] = ['f.storage_name', 'like', '%'.$data['keyword'].'%'];
                    break;
                case 4://序列号
                    $keywordCondition[] = ['a.serial_number', 'like', '%'.$data['keyword'].'%'];
                    break;
                case 5://操作人名称
                    $keywordCondition[] = ['d.nickname', 'like', '%'.$data['keyword'].'%'];
                    break;
            }
        }

        switch($data['type']) {
            case 0:
                self::inLogExport($data, $spreadsheet, $sheet, $keywordCondition);
                break;
            case 1:
                self::outLogExport($data, $spreadsheet, $sheet, $keywordCondition);
                break;
        }

        ob_end_clean();
        return true;
    }

    private static function inLogExport($data, $spreadsheet, $sheet, $keywordCondition)
    {
        $logData = LogService::inStorageExportData($data, $keywordCondition);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:U1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:O')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:O')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '入库时间')
            ->setCellValue('C1', '商品信息')
            ->setCellValue('D1', '商品类型')
            ->setCellValue('E1', '序列号')
            ->setCellValue('F1', '操作数量')
            ->setCellValue('G1', '操作后数量')
            ->setCellValue('H1', '操作后仓库')
            ->setCellValue('I1', '入库类型')
            ->setCellValue('J1', '订单编号')
            ->setCellValue('K1', '操作前数量')
            ->setCellValue('L1', '操作前仓库')
            ->setCellValue('M1', '备注')
            ->setCellValue('N1', '标签理由')
            ->setCellValue('O1', '操作时间');

        $goodsNum = [];
        $list = [];
        foreach($logData as $commonValue) {
            if(!empty($commonValue['content'])) {
                $goodsNum[] = count($commonValue['content']);

                $type = '';
                switch ($commonValue['type']) {
                    case 0:
                        $type = '入库';
                        break;
                    case 1:
                        $type = '调拨入库';
                        break;
                    case 2:
                        $type = '直接入库';
                        break;
                    case 3:
                        $type = '生产订单还库';
                        break;
                    case 4:
                        $type = '扫码入库';
                        break;
                }

                $allType = '';
                $purchaseNo = '';
                switch ($commonValue['all_id_type']) {
                    case 0:
                        $allType = '';
                        break;
                    case 1:
                        $allType = '报溢';
                        break;
                    case 2:
                        $purchaseNo = SalesReturnModel::where('id', $commonValue['all_id'])
                            ->value('order_no');
                        $allType = '退货';
                        break;
                    case 3:
                        $purchaseNo = MaterielModel::where('id', $commonValue['all_id'])
                            ->value('purchase_no');
                        $allType = '采购';
                        break;
                    case 4:
                        $purchaseNo = ProductOrderModel::where('id', $commonValue['all_id'])
                            ->value('product_no');
                        $allType = '生产订单';
                        break;
                    case 6:
                        $allType = '组件返仓';
                        break;
                }

                $sheet->setCellValue('A1', '操作人')
                    ->setCellValue('B1', '入库时间')
                    ->setCellValue('C1', '商品信息')
                    ->setCellValue('D1', '商品类型')
                    ->setCellValue('E1', '序列号')
                    ->setCellValue('F1', '操作数量')
                    ->setCellValue('G1', '操作后数量')
                    ->setCellValue('H1', '操作后仓库')
                    ->setCellValue('I1', '入库类型')
                    ->setCellValue('J1', '订单编号')
                    ->setCellValue('K1', '操作前数量')
                    ->setCellValue('L1', '操作前仓库')
                    ->setCellValue('M1', '备注')
                    ->setCellValue('N1', '标签理由')
                    ->setCellValue('O1', '操作时间');

                foreach($commonValue['content'] as $contentValue) {
                    $list[] = [
                        'nickname' => $commonValue['nickname'],
                        'operate_time' => $commonValue['operate_time'],
                        'materiel_name' => $contentValue['materiel_name'].'（'.$contentValue['materiel_no'].')',
                        'type_name' => $contentValue['type_name'],
                        'serial_number' => $contentValue['serial_number'],
                        'operate_num' => $contentValue['operate_num'],
                        'next_storage_num' => $contentValue['next_storage_num'],
                        'next_storage_name' => $contentValue['next_storage_name'].'/'.$contentValue['shelf_name'].'/'.$contentValue['location_name'],
                        'type' => $allType.$type,
                        'purchase_no' => $purchaseNo,
                        'last_storage_num' => $contentValue['last_storage_num'],
                        'storage_name' => $contentValue['storage_name'].'/'.$contentValue['shelf_name'].'/'.$contentValue['location_name'],
                        'remark' => $contentValue['remark'],
                        'label_name' => $contentValue['label_name'],
                        'created_at' => $commonValue['created_at'],
                    ];
                }
            }
        }

        $cell = ['A', 'B','G','H','M'];//需要合并的单元格

        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value)-1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }

            foreach($list as $key => $value) {
                if($key/300 == 0){
                    ob_flush();
                    flush();
                }

                $key += 2;

                $sheet->setCellValue('A'.$key, $value['nickname'])
                    ->setCellValue('B'.$key, $value['operate_time'])
                    ->setCellValue('C'.$key, $value['materiel_name'])
                    ->setCellValue('D'.$key, $value['type_name'])
                    ->setCellValue('E'.$key, $value['serial_number'])
                    ->setCellValue('F'.$key, $value['operate_num'])
                    ->setCellValue('G'.$key, $value['next_storage_num'])
                    ->setCellValue('H'.$key, $value['storage_name'])
                    ->setCellValue('I'.$key, $value['type'])
                    ->setCellValue('J'.$key, $value['purchase_no'])
                    ->setCellValue('K'.$key, $value['last_storage_num'])
                    ->setCellValue('L'.$key, $value['next_storage_name'])
                    ->setCellValue('M'.$key, $value['remark'])
                    ->setCellValue('N'.$key, $value['label_name'])
                    ->setCellValue('O'.$key, $value['created_at']);
            }
        }

        return self::downloadExcel($spreadsheet, '入库记录表_'.date('Y-m-d'), 'Xlsx');
    }

    private static function outLogExport($data, $spreadsheet, $sheet, $keywordCondition)
    {
        $logData = LogService::outStorageExportData($data, $keywordCondition);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:O1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:O')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:O')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('K')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('L')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '出库时间')
            ->setCellValue('C1', '商品信息')
            ->setCellValue('D1', '商品类型')
            ->setCellValue('E1', '序列号')
            ->setCellValue('F1', '操作数量')
            ->setCellValue('G1', '操作后数量')
            ->setCellValue('H1', '操作后仓库')
            ->setCellValue('I1', '出库类型')
            ->setCellValue('J1', '订单编号')
            ->setCellValue('K1', '操作前数量')
            ->setCellValue('L1', '操作前仓库')
            ->setCellValue('M1', '备注')
            ->setCellValue('N1', '标签理由')
            ->setCellValue('O1', '操作时间');

        $goodsNum = [];
        $list = [];
        foreach($logData as $commonValue) {
            if(!empty($commonValue['content'])) {
                $goodsNum[] = count($commonValue['content']);

                $type = '';
                switch ($commonValue['type']) {
                    case 0:
                        $type = '出库';
                        break;
                    case 1:
                        $type = '调拨出库';
                        break;
                    case 2:
                        $type = '直接出库';
                        break;
                    case 3:
                        $type = '生产订单补足';
                        break;
                    case 4:
                        $type = '扫码出库';
                        break;
                }

                $allType = '';
                $purchaseNo = '';
                switch ($commonValue['all_id_type']) {
                    case 0:
                        $allType = '';
                        break;
                    case 1:
                        $allType = '报损';
                        break;
                    case 2:
                        $purchaseNo = SellModel::where('id', $commonValue['all_id'])
                            ->value('order_no');
                        $allType = '销售';
                        break;
                    case 3:
                        $purchaseNo = ProductOrderModel::where('id', $commonValue['all_id'])
                            ->value('product_no');
                        $allType = '生产订单出库';
                        break;
                }

                foreach($commonValue['content'] as $contentValue) {
                    $list[] = [
                        'nickname' => $commonValue['nickname'],
                        'operate_time' => $commonValue['operate_time'],
                        'materiel_name' => $contentValue['materiel_name'].'（'.$contentValue['materiel_no'].'）',
                        'type_name' => $contentValue['type_name'],
                        'serial_number' => $contentValue['serial_number'],
                        'operate_num' => $contentValue['operate_num'],
                        'next_storage_num' => $contentValue['next_storage_num'],
                        'next_storage_name' => $contentValue['next_storage_name'].'/'.$contentValue['shelf_name'].'/'.$contentValue['location_name'],
                        'type' => $allType.$type,
                        'purchase_no' => $purchaseNo,
                        'last_storage_num' => $contentValue['last_storage_num'],
                        'storage_name' => $contentValue['storage_name'].'/'.$contentValue['shelf_name'].'/'.$contentValue['location_name'],
                        'remark' => $contentValue['remark'],
                        'label_name' => $contentValue['label_name'],
                        'created_at' => $commonValue['created_at'],
                    ];
                }
            }
        }

        $cell = ['A', 'B','G','H','M'];//需要合并的单元格

        if(!empty($list)){
            foreach ($cell as $item) {
                $start = 2;
                foreach ($goodsNum as $key => $value) {
                    $end = ($start + $value)-1;

                    $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                    $start = $end + 1;
                }

            }

            foreach($list as $key => $value) {
                if($key/300 == 0){
                    ob_flush();
                    flush();
                }

                $key += 2;
                $sheet->setCellValue('A'.$key, $value['nickname'])
                    ->setCellValue('B'.$key, $value['operate_time'])
                    ->setCellValue('C'.$key, $value['materiel_name'])
                    ->setCellValue('D'.$key, $value['type_name'])
                    ->setCellValue('E'.$key, $value['serial_number'])
                    ->setCellValue('F'.$key, $value['operate_num'])
                    ->setCellValue('G'.$key, $value['next_storage_num'])
                    ->setCellValue('H'.$key, $value['storage_name'])
                    ->setCellValue('I'.$key, $value['type'])
                    ->setCellValue('J'.$key, $value['purchase_no'])
                    ->setCellValue('K'.$key, $value['last_storage_num'])
                    ->setCellValue('L'.$key, $value['next_storage_name'])
                    ->setCellValue('M'.$key, $value['remark'])
                    ->setCellValue('N'.$key, $value['label_name'])
                    ->setCellValue('O'.$key, $value['created_at']);
            }
        }

        return self::downloadExcel($spreadsheet, '出库记录表_'.date('Y-m-d'), 'Xlsx');
    }

    /**
     * 出入库操作记录导出 ----v3.2.6废弃
     * @param Request $request
     * @return bool|\Illuminate\Http\JsonResponse
     * @Author: Sun Wu
     * @Created at: 2022/10/29 17:15
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     *
     */
    public function storageStartExportAbandon(Request $request)
    {
        $token = $request->header('token');
        $userInfo = self::checkToken($token);

        $validator = Validator::make($data = $request->all(), [
            'type' => [
                'required',
                'integer',
                Rule::In([0,1,2]),
            ],//操作日志类型;0:入库;1:出库;2:套餐出库;
            'status' => [
                'required',
                'integer',
                Rule::In([0,1]),
            ],//撤销状态(0:未撤销;1:已撤销;)
        ],
            [
                'type.required' => '参数`日志类型`缺失',
                'type.integer' => '参数`日志类型`只能为数字',
                'type.in' => '参数`日志类型`不合法',
            ]
        );

        if ($info = $validator->errors()->first()) {
            return response()->json([
                'code' => 1,
                'msg' => $info,
                'data' => '',
            ]);
        }

        @ini_set('memory_limit', '1024M');
        @ini_set('max_execution_time', 30);
        ob_end_clean();
        ob_start();
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $spreadsheet->getDefaultStyle();
        $sheet->setTitle('出入库记录报表');

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:T1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        if(!$userInfo['userType']) {
            $user['a.main_id'] = $userInfo['mainUserId'];
        }
        else {
            $user['a.user_id'] = $userInfo['userId'];
        }

        switch($data['type']) {
            case 0:
                self::inLogExport($data, $user, $spreadsheet, $sheet);
                break;
            case 1:
                self::outLogExport($data, $user, $spreadsheet, $sheet);
                break;
            default:
               self::packageLogExport($data, $user, $spreadsheet, $sheet);
        }

        return true;
    }

    //废弃
    private function inLogExportAbandon(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $logData = LogService::inLogExport($data, $user);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:W1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:W')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:W')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(20);

        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('U')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('V')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('W')->getAlignment()->setWrapText(true);

        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '商品名')
            ->setCellValue('C1', '商品规格')
            ->setCellValue('D1', '商品成本')
            ->setCellValue('E1', '序列号')
            ->setCellValue('F1', '批次号')
            ->setCellValue('G1', '上一批次号')
            ->setCellValue('H1', '调拨编号')
            ->setCellValue('I1', '当前仓库名')
            ->setCellValue('J1', '当前仓库地址')
            ->setCellValue('K1', '当前货架')
            ->setCellValue('L1', '当前库位')
            ->setCellValue('M1', '上一仓库名')
            ->setCellValue('N1', '上一仓库地址')
            ->setCellValue('O1', '上一货架')
            ->setCellValue('P1', '上一库位')
            ->setCellValue('Q1', '操作前数量')
            ->setCellValue('R1', '操作数量')
            ->setCellValue('S1', '操作后数量')
            ->setCellValue('T1', '类型')
            ->setCellValue('U1', '操作人')
            ->setCellValue('V1', '出库时间')
            ->setCellValue('W1', '操作时间');

        foreach($logData as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A'.$key, $value['materiel_img'])
                ->setCellValue('B'.$key, $value['materiel_name'])
                ->setCellValue('C'.$key, $value['type_name'])
                ->setCellValue('D'.$key, $value['materiel_mean_cost'])
                ->setCellValue('E'.$key, empty($value['serial_number'])?'':$value['serial_number'])
                ->setCellValue('F'.$key, empty($value['batch_no'])?'':$value['batch_no'])
                ->setCellValue('G'.$key, empty($value['last_batch_no'])?'':$value['last_batch_no'])
                ->setCellValue('H'.$key, empty($value['allocate_no'])?'':$value['allocate_no'])
                ->setCellValue('I'.$key, $value['storage_name'])
                ->setCellValue('J'.$key, $value['storage_address'])
                ->setCellValue('K'.$key, $value['shelf_name'])
                ->setCellValue('L'.$key, $value['location_name'])
                ->setCellValue('M'.$key, empty($value['last_storage_name'])?'':$value['last_storage_name'])
                ->setCellValue('N'.$key, empty($value['last_storage_address'])?'':$value['last_storage_address'])
                ->setCellValue('O'.$key, empty($value['last_shelf_name'])?'':$value['last_shelf_name'])
                ->setCellValue('P'.$key, empty($value['last_location_name'])?'':$value['last_location_name'])
                ->setCellValue('Q'.$key, $value['last_storage_num'])
                ->setCellValue('R'.$key, $value['operate_num'])
                ->setCellValue('S'.$key, $value['next_storage_num'])
                ->setCellValue('T'.$key, $value['type'])
                ->setCellValue('U'.$key, $value['nickname'])
                ->setCellValue('V'.$key, $value['in_storage_time'])
                ->setCellValue('W'.$key, $value['created_at']);

            switch($value['type']) {
                case 0:
                    $value['type'] = '入库';
                    break;
                case 1:
                    $value['type'] = '调拨入库';
                    break;
                case 2:
                    $value['type'] = '直接入库';
                    break;
            }
            $sheet->setCellValue('R'.$key, $value['type']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '入库操作记录报表_'.date('Y-m-d'), 'Xlsx');
        return true;
    }

    private function outLogExportAbandon(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $logData = LogService::outLogExport($data, $user);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:W1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:X')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:X')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(20);
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('R')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('S')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('T')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('U')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('V')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('W')->getAlignment()->setWrapText(true);

        $sheet->setCellValue('A1', '商品图')
            ->setCellValue('B1', '商品名')
            ->setCellValue('C1', '商品规格')
            ->setCellValue('D1', '商品编号')
            ->setCellValue('E1', '商品成本')
            ->setCellValue('F1', '序列号')
            ->setCellValue('G1', '批次号')
            ->setCellValue('H1', '上一批次号')
            ->setCellValue('I1', '调拨编号')
            ->setCellValue('J1', '当前仓库名')
            ->setCellValue('K1', '当前仓库地址')
            ->setCellValue('L1', '当前库位')
            ->setCellValue('M1', '当前货架')
            ->setCellValue('N1', '下一仓库名')
            ->setCellValue('O1', '下一仓库地址')
            ->setCellValue('P1', '下一库位')
            ->setCellValue('Q1', '下一货架')
            ->setCellValue('R1', '操作前数量')
            ->setCellValue('S1', '操作数量')
            ->setCellValue('T1', '操作后数量')
            ->setCellValue('U1', '类型')
            ->setCellValue('V1', '出库时间')
            ->setCellValue('W1', '操作人')
            ->setCellValue('X1', '操作时间');

        foreach($logData as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A'.$key, $value['materiel_img'])
                ->setCellValue('B'.$key, $value['materiel_name'])
                ->setCellValue('C'.$key, $value['type_name'])
                ->setCellValue('D'.$key, $value['materiel_no'])
                ->setCellValue('E'.$key, $value['materiel_mean_cost'])
                ->setCellValue('F'.$key, empty($value['serial_number'])?'':$value['serial_number'])
                ->setCellValue('G'.$key, empty($value['batch_no'])?'':$value['batch_no'])
                ->setCellValue('H'.$key, empty($value['before_allocate_batch_no'])?'':$value['before_allocate_batch_no'])
                ->setCellValue('I'.$key, empty($value['allocate_no'])?'':$value['allocate_no'])
                ->setCellValue('J'.$key, $value['storage_name'])
                ->setCellValue('K'.$key, $value['storage_address'])
                ->setCellValue('L'.$key, $value['shelf_name'])
                ->setCellValue('M'.$key, $value['location_name'])
                ->setCellValue('N'.$key, empty($value['next_storage_name'])?'':$value['next_storage_name'])
                ->setCellValue('O'.$key, empty($value['next_storage_address'])?'':$value['next_storage_address'])
                ->setCellValue('P'.$key, empty($value['next_shelf_name'])?'':$value['next_shelf_name'])
                ->setCellValue('Q'.$key, empty($value['next_location_name'])?'':$value['next_location_name'])
                ->setCellValue('R'.$key, $value['last_storage_num'])
                ->setCellValue('S'.$key, $value['operate_num'])
                ->setCellValue('T'.$key, $value['next_storage_num'])
                ->setCellValue('V'.$key, $value['nickname'])
                ->setCellValue('W'.$key, $value['out_storage_time'])
                ->setCellValue('X'.$key, $value['created_at']);

            switch($value['type']) {
                case 0:
                    $value['type'] = '出库';
                    break;
                case 1:
                    $value['type'] = '调拨出库';
                    break;
                case 2:
                    $value['type'] = '直接出库';
                    break;
            }
            $sheet->setCellValue('U'.$key, $value['type']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '出库操作记录报表_'.date('Y-m-d'), 'Xlsx');
        return true;
    }

    private function packageLogExport(array $data, array $user, Spreadsheet $spreadsheet, Worksheet $sheet)
    {
        $getPackageId = LogService::getPackageId($data, $user);

        $packageId = [];
        foreach($getPackageId as $value) {
            $packageId[] = $value['id'];
        }

        $logData = LogService::packageOutLogExport($packageId);

        //A1-L1字体加粗
        $spreadsheet->getActiveSheet()->getStyle('A1:W1')->getFont()->setBold(true);
        //设置字体
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体');
        //设置字体大小
        $spreadsheet->getDefaultStyle()->getFont()->setSize(12);
        //设置默认行高
        $spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(40);

        $spreadsheet->getActiveSheet()->getStyle('A:X')->getAlignment()->setHorizontal('center');
        $spreadsheet->getActiveSheet()->getStyle('A:X')->getAlignment()->setVertical('center');

        //设置列宽
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(40);
        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('B')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('C')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('D')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('E')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('F')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('G')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('H')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('I')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('J')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('M')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('N')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('O')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('P')->getAlignment()->setWrapText(true);
        $spreadsheet->getActiveSheet()->getStyle('Q')->getAlignment()->setWrapText(true);

        $sheet->setCellValue('A1', '操作人')
            ->setCellValue('B1', '套餐名')
            ->setCellValue('C1', '套餐操作数量')
            ->setCellValue('D1', '商品图')
            ->setCellValue('E1', '商品名')
            ->setCellValue('F1', '规格名')
            ->setCellValue('G1', '出库仓库名')
            ->setCellValue('H1', '出库仓库地址')
            ->setCellValue('I1', '出库货架')
            ->setCellValue('J1', '出库库位')
            ->setCellValue('K1', '出库前数量')
            ->setCellValue('L1', '商品出库数量')
            ->setCellValue('M1', '出库后数量')
            ->setCellValue('N1', '批次号')
            ->setCellValue('O1', '备注')
            ->setCellValue('P1', '出库时间')
            ->setCellValue('Q1', '操作时间');

        $cell = ['A', 'B', 'C', 'O','P','Q'];//需要合并的单元格

        foreach ($cell as $item) {
            $start = 2;
            foreach ($logData['data2'] as $k => $value) {
                $end = ($start + $value)-1;
                $sheet->mergeCells($item . $start . ':' . $item . $end); //合并单元格
                $start = $end + 1;
            }
        }

        foreach($logData['data1'] as $key => $value) {
            $key += 2;

            $sheet->setCellValue('A'.$key, $value['nickname'])
                ->setCellValue('B'.$key, $value['package_name'])
                ->setCellValue('C'.$key, $value['num'])
                ->setCellValue('D'.$key, $value['materiel_img'])
                ->setCellValue('E'.$key, $value['materiel_name'])
                ->setCellValue('F'.$key, $value['type_name'])
                ->setCellValue('G'.$key, $value['storage_name'])
                ->setCellValue('H'.$key, $value['storage_address'])
                ->setCellValue('I'.$key, $value['shelf_name'])
                ->setCellValue('J'.$key, $value['location_name'])
                ->setCellValue('K'.$key, $value['last_storage_num'])
                ->setCellValue('L'.$key, $value['operate_num'])
                ->setCellValue('M'.$key, $value['next_storage_num'])
                ->setCellValue('N'.$key, empty($value['batch_no'])?'':$value['batch_no'])
                ->setCellValue('O'.$key, empty($value['remark'])?'':$value['remark'])
                ->setCellValue('P'.$key, empty($value['out_storage_time'])?'':$value['out_storage_time'])
                ->setCellValue('Q'.$key, empty($value['created_at'])?'':$value['created_at']);
        }
        ob_end_clean();

        self::downloadExcel($spreadsheet, '套餐出库操作记录报表_'.date('Y-m-d'), 'Xlsx');
        return true;
    }


    /**
     * 物料分类导入（zlf）
     * @param Request $request
     */
    public function goodClassImpart(Request $request){

        $token = $request->header('token');

        if(empty($token)){
            return response()->json(['code' => 2,'msg' => 'token不能为空','data' => '']);
        }

        $tokenInfo = self::checkToken($token);
        if(!$tokenInfo) {
            return $tokenInfo;
        }

        $mainId = $tokenInfo['mainUserId'];

        $files = $request->file('class_templete');

        $ext = $files->extension();

        if($files->getSize() > 10 * 1024 * 1024)
        {
            return response()->json(['code' => 1,'msg'  => '文件大小不能超出10M','data' => '']);
        }

        //application/vnd.ms-excel  为xls或xlsx文件类型
        if (!in_array($ext, ['xls', 'xlsx'])) {
            return response()->json(['code' => 1,'msg' => '必须为excel表格，且必须为xlsx或xls格式！','data' => '']);
        }

        if($ext == 'xls') {
            $reader = IOFactory::createReader('Xls');
        }
        else if($ext == 'xlsx') {
            $reader = IOFactory::createReader('Xlsx');
        }

        $path = $files->store(date('Y').'/'.date('m').'/'.date('d'));
        $filePath     = public_path().'/uploads/'.$path;

        $objPHPExcel  = $reader->load($filePath);
        $excelSheet   = $objPHPExcel->getSheet(0);//读取第一个工作表
        $highestColumn = $excelSheet->getHighestColumn();   // 取得总列数

        $highestRow   = $excelSheet->getHighestRow();// 取得总行数
        $eData = [];
        $n = $objPHPExcel->getActiveSheet()->getCell('A1')->getValue();

        for ($j = 2; $j <= $highestRow; $j++) {
            $n = $j-2;
            $eData[$n] = [
                'class_no'    => $objPHPExcel->getActiveSheet()->getCell('B' . $j)->getValue(),
                'class_name'      => $objPHPExcel->getActiveSheet()->getCell('C' . $j)->getValue(),
            ];
        }

        if(empty($eData)) {
            return response()->json(['code' => 0,'msg' => 'success','data' => '']);
        }
        DB::beginTransaction();

        try {
            $arr = [];
            $count = count($eData);
            for($p = 0;$p < $count; $p++) {
                $a = $eData[$p];
                unset($eData[$p]);
                if(!in_array($a, $eData)) {
                    $arr[] = $a;
                }
            };

            $classData['parent_id']  = 0;
            $classData['created_at'] = time();
            $classData['main_id']    = $mainId;
            foreach($arr as $key => $value) {
                $i=$key+2;
                $checkNoInfo = MaterielClassModel::where([
                    'parent_id' =>  0,
                    'class_no' => $value['class_no'],
                    'main_id' => $mainId,
                    'delete_flag' => 0
                ])->first();

                if(!empty($checkNoInfo)) {
                    return ['code' => 1, 'msg' => '第'.$i.'行,分类编号`'.$value['class_no'].'`已存在','data'=>''];
                }

                $checkNameInfo = MaterielClassModel::where([
                    'parent_id' =>  0,
                    'class_name' => $value['class_name'],
                    'main_id' => $mainId,
                    'delete_flag' => 0
                ])->first();

                if(!empty($checkNameInfo)) {
                    return ['code' => 1, 'msg' => '第'.$i.'行,分类`'.$value['class_name'].'`已存在','data'=>''];
                }
                $classData['class_no']   = $value['class_no'];
                $classData['class_name'] = $value['class_name'];

                $id = MaterielClassModel::insertGetId($classData);
                if(!$id) return ['code' => 1, 'msg' => '第'.$i.'行，导入失败','data'=>''];
            };

            DB::commit();
            unlink($filePath);
            return response()->json(['code' => 0,'msg' => 'success','data' => '']);
        }catch (\Exception $e) {
            DB::rollBack();
            Log::debug('导入分类信息excel异常:'.var_export($e->getMessage(), true));
            return response()->json(['code' => 1,'msg' => '导入分类信息excel时发生异常，请稍后再试','data' => '']);
        }
    }

    /**
     * 物料分类模板导出（zlf）
     * @param Request $request
     */
    public function classTempDownload(){
        ob_end_clean();
        return response()->download(public_path().'/class_temp.xls');
    }
}
